VBA - Folder Picker - set where to start

Question

I have a small VBA application for access that requires users to select a folder. I was wondering if there is a way to tell VBA how to start the folder collector. that is, start the folder collector in C:\data\forms . Currently, it seems to start with the directory that was previously used. There is also a way to restrict access to the folder. That way, he can access something in C:\data , but not anything else in C:

Answer 1

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

 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 

Answer 2

Here is a quick and dirty method that I use all the time. The function below will allow the user to select the folder in which they will be launched - I think the easiest way to restrict access to this path is to possibly check GetFolderName below for the path (s) you want to restrict, for example

 If GetFolderName = "C:\" then MsgBox("This folder is not for you buddy") Exit Sub end if 

Also not my code :)

 Public Function GetFolderName(Optional OpenAt As String) As String Dim lCount As Long GetFolderName = vbNullString With Application.FileDialog(msoFileDialogFolderPicker) .InitialFileName = OpenAt .Show For lCount = 1 To .SelectedItems.Count GetFolderName = .SelectedItems(lCount) Next lCount End With End Function 

Answer 3

If you do not need to limit the viewing of the folder to your user, I would suggest using the FileDialog method (the interface is more intuitive and then calls the shell). For more information, visit CPearson. He has a long article on browsing folders using VBA (mulitple ways, the FileDialog option is at the very end): http://www.cpearson.com/excel/browsefolder.aspx

Answer 4

Here is a much simpler way. This code fragment allows the user to select a folder and then print the address of this folder on the screen:

 Sub PrintSelectedFolder() Dim selectedFolder With Application.FileDialog(msoFileDialogFolderPicker) .Show selectedFolder = .SelectedItems(1) End With 'print to screen the address of folder selected MsgBox (selectedFolder) End Sub 

Answer 5

For Mac users:

 Sub Select_Folder_On_Mac() Dim folderPath As String Dim RootFolder As String On Error Resume Next RootFolder = MacScript("return (path to desktop folder) as String") 'Or use RootFolder = "Macintosh HD:Users:YourUserName:Desktop:TestMap:" folderPath = MacScript("(choose folder with prompt ""Select the folder""" & _ "default location alias """ & RootFolder & """) as string") On Error GoTo 0 If folderPath <> "" Then MsgBox folderPath End If End Sub 

Stolen from http://www.rondebruin.nl/mac/mac017.htm ;)