VBA – Folder Picker – set where to start [duplicate]

by

I have been using the following code (Not My Code) successfully for many years.

enter image description here

Sub Sample()
    Dim Ret

    '~~> Specify your start folder here
    Ret = BrowseForFolder("C:\")
End Sub

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
     'Function purpose:  To Browser for a user selected folder.
     'If the "OpenAt" path is provided, open the browser at that directory
     'NOTE:  If invalid, it will open at the Desktop level

    Dim ShellApp As Object

     'Create a file browser window at the default folder
    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

     'Set the folder to that selected.  (On error in case cancelled)
    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0

     'Destroy the Shell Application
    Set ShellApp = Nothing

     'Check for invalid or non-entries and send to the Invalid error
     'handler if found
     'Valid selections can begin L: (where L is a letter) or
     '\\ (as in \\servername\sharename.  All others are invalid
    Select Case Mid(BrowseForFolder, 2, 1)
    Case Is = ":"
        If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
    Case Is = "\"
        If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
    Case Else
        GoTo Invalid
    End Select

    Exit Function

Invalid:
     'If it was determined that the selection was invalid, set to False
    BrowseForFolder = False
End Function

More Related Contents:

  1. ms access vba code for update records through form
  2. How to add default signature in Outlook
  3. Combine rows / concatenate rows
  4. Is it possible to pass parameters programmatically in a Microsoft Access update query?
  5. Getting Error 3340 Query ‘ ‘ is corrupt while executing queries DoCmd.RunSQL
  6. Too few parameters Expected 1, recordset issue
  7. Show “Open File” Dialog
  8. How do I display a messagebox with unicode characters in VBA?
  9. Determining whether an object is a member of a collection in VBA
  10. Distinction between using .text and .value in VBA Access
  11. MS Access VBA Error: Run time error ’70’ Permission Denied
  12. Automatically generating handling of issues
  13. Is it better to use ADO or DAO in Access 2007?
  14. MS Access: how to compact current database in VBA
  15. How to loop through all controls in a form, including controls in a subform – Access 2007
  16. FileDialog doesn’t work
  17. Working with Unicode file names in VBA (using Dir, FileSystemObject, etc.)
  18. Determine real cause of ODBC failure (error 3146) with ms-access?
  19. Is there an equivalent to Thread.Sleep() in VBA
  20. Exporting MS Access Forms and Class / Modules Recursively to text files?
  21. How to get logged-in user’s name in Access vba? [duplicate]
  22. Pass a parameter into an access report programmatically
  23. finding latest file in a folder and opening it (vba access)
  24. Insert multiple records with a date range in MS Access
  25. MS Access VBA: Sending an email through Outlook [closed]
  26. SendKeys is messing with my NumLock key via VBA code in Access form
  27. Runtime error 287. Sending Emails through Outlook using VBA in Access
  28. Programmatic Access To Visual Basic Project Is Not Trusted
  29. Controlling IE11 “Do you want to Open/Save” dialogue window buttons in VBA
  30. VBA Macro to compare all cells of two Excel files

Leave a Comment