Browse For Folder
This page describes how to display a dialog box to prompt the user to select a folder.
While Excel has a built in File Open method (GetOpenFileName),
it does not provide a method to browse for a folder. This page desribes three methods you can use to prompt the user to select a folder. The first method uses
Windows API functions to display Windows' standard Browse Folder dialog and
requires no additional references. The second method uses the Shell Controls
object library. The thrid method uses the Application's FileDialog member.
You can download a module file containing the code
described on this page.
Copy the following code into a standard code module.
Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260
Type BrowseInfo
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszINSTRUCTIONS As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
Type SHFILEOPSTRUCT
hwnd As Long
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAnyOperationsAborted As Boolean
hNameMappings As Long
lpszProgressTitle As String
End Type
Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
ByVal pidl As Long, _
ByVal pszBuffer As String) As Long
Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
lpBrowseInfo As BrowseInfo) As Long
Function BrowseFolder(Optional Caption As String = "") As String
Dim BrowseInfo As BrowseInfo
Dim FolderName As String
Dim ID As Long
Dim Res As Long
With BrowseInfo
.hOwner = 0
.pidlRoot = 0
.pszDisplayName = String$(MAX_PATH, vbNullChar)
.lpszINSTRUCTIONS = Caption
.ulFlags = BIF_RETURNONLYFSDIRS
.lpfn = 0
End With
FolderName = String$(MAX_PATH, vbNullChar)
ID = SHBrowseForFolderA(BrowseInfo)
If ID Then
Res = SHGetPathFromIDListA(ID, FolderName)
If Res Then
BrowseFolder = Left$(FolderName, InStr(FolderName,vbNullChar) - 1)
End If
End If
End Function
You can then call the BrowseFolder function with code
like the following:
Dim FName As String
FName = BrowseFolder(Caption:="Select A Folder")
If FName = vbNullString Then
Debug.Print "No Folder Selected"
Else
Debug.Print "Selected Folder: " & FName
End If
First, you must set a reference to the "Microsoft Shell Controls And Automation"
library. In VBA, go to the Tools menu, choose References, and scroll down in the
list to "Microsoft Shell Controls And Automation" and check the checkbox. Then,
copy the following code into a standard code module.
Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260
Function BrowseFolder(Optional Caption As String, _
Optional InitialFolder As String) As String
Dim SH As Shell32.Shell
Dim F As Shell32.Folder
Set SH = New Shell32.Shell
Set F = SH.BrowseForFolder(0&, Caption, BIF_RETURNONLYFSDIRS, InitialFolder)
If Not F Is Nothing Then
BrowseFolder = F.Items.Item.Path
End If
End Function
You can call the function above with code like the following:
Dim FName As String
FName = BrowseFolder(Caption:="Select A
Folder",InitialFolder:="C:\MyFolder")
If FName = vbNullString Then
Debug.Print "No folder selected."
Else
Debug.Print "Folder Selected: " & FName
End If
You can use the following code to display the application's FileDialog to select as folder.
Function BrowseFolder(Title As String, _
Optional InitialView As Office.MsoFileDialogView = _
msoFileDialogViewList) As String
Dim V As Variant
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = Title
.InitialView = InitialView
.Show
On Error Resume Next
Err.Clear
V = .SelectedItems(1)
If Err.Number <> 0 Then
V = vbNullString
End If
End With
BrowseFolder = CStr(V)
End Function
This function takes two parameters. The first, Title is a string specifying the title
to be displayed with the file dialog. The second, which is optional, specifies the view type. See
MsoFileDialogView in the Object Browser for the valid values of this parameter. The
function returns the fully-qualified folder name selected by the user or an empty string if the user cancelled
the dialog.
You can download a module file containing the code
described on this page.
This page last modified on 5-July-2007.