Browse For Folder

This page describes how to display a dialog box to prompt the user to select a folder.

Introduction

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.

Using The Windows API Functions

Copy the following code into a standard code module.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' modBrowseFolder
' This contains the BrowseFolder function, which displays the standard Windows Browse For Folder
' dialog. It return the complete path of the selected folder or vbNullString if the user cancelled.
' It also contains the function BrowseFolderExplorer which presents the user with a Windows
' Explorer-like interface to pick the folder.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
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 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


Private Declare Function SHGetPathFromIDListA Lib "shell32.dll" (ByVal pidl As Long, _
    ByVal pszBuffer As String) As Long

Private Declare Function SHBrowseForFolderA Lib "shell32.dll" (lpBrowseInfo As _
    BROWSEINFO) As Long


Private Const MAX_PATH = 260 ' Windows mandated


Function BrowseFolder(Optional ByVal DialogTitle As String) As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' BrowseFolder
' This displays the standard Windows Browse Folder dialog. It returns
' the complete path name of the selected folder or vbNullString if the
' user cancelled.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If DialogTitle = vbNullString Then
    DialogTitle = "Select A Folder"
End If

Dim uBrowseInfo As BROWSEINFO
Dim szBuffer As String
Dim lID As Long
Dim lRet As Long

With uBrowseInfo
    .hOwner = 0
    .pidlRoot = 0
    .pszDisplayName = String$(MAX_PATH, vbNullChar)
    .lpszINSTRUCTIONS = DialogTitle
    .ulFlags = BIF_RETURNONLYFSDIRS ' + BIF_USENEWUI
    .lpfn = 0
End With
szBuffer = String$(MAX_PATH, vbNullChar)
lID = SHBrowseForFolderA(uBrowseInfo)

If lID Then
    ''' Retrieve the path string.
    lRet = SHGetPathFromIDListA(lID, szBuffer)
    If lRet Then
        BrowseFolder = Left$(szBuffer, InStr(szBuffer, 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

You can specify a starting folder for the BrowseFolder function by specifying a CSIDL value for one of the standard, Windows defined folders. For example, you can set the initial diretory to the Program Files folder. Two notes regarding specifying an initial folder: first, it must be one of the standard Windows folders (e.g., My Documents), not an arbitrary folder, and second, the user cannot browse above the folder. The folder is the top-most level available to the Browse Folder dialog. To specify and standard Windows folder, set the pidlRoot member of the BrowseInfo structure to a valid CSIDL. For example,

BrowseInfo.pidlRoot = &H26 ' CSIDL_PROGRAM_FILES 

A modified version of BrowseFolder that accepts a root CSIDL is shown below. If the RootCSIDL parameter is omitted, it defaults to 0, which the desktop virtual folder. Note that there is no error checking to ensure that the RootCSIDL parameter contians a valid value. If RootCSIDL is not valid, the dialog is not displayed and BrowseFolder returns an empty string. A list of valid CSIDL named constants is shown after the code.

Function BrowseFolder2(Optional ByVal DialogTitle As String, _
    Optional RootCSIDL As Long = 0) As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' BrowseFolder
' This displays the standard Windows Browse Folder dialog. It returns
' the complete path name of the selected folder or vbNullString if the
' user cancelled.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim uBrowseInfo As BROWSEINFO
Dim szBuffer As String
Dim lID As Long
Dim lRet As Long


If DialogTitle = vbNullString Then
    DialogTitle = "Select A Folder"
End If

With uBrowseInfo
    .hOwner = 0
    .pidlRoot = RootCSIDL
    .pszDisplayName = String$(MAX_PATH, vbNullChar)
    .lpszINSTRUCTIONS = DialogTitle
    .ulFlags = BIF_RETURNONLYFSDIRS ' + BIF_USENEWUI
    .lpfn = 0
End With
szBuffer = String$(MAX_PATH, vbNullChar)
lID = SHBrowseForFolderA(uBrowseInfo)

If lID Then
    ''' Retrieve the path string.
    lRet = SHGetPathFromIDListA(lID, szBuffer)
    If lRet Then
        BrowseFolder2 = Left$(szBuffer, InStr(szBuffer, vbNullChar) - 1)
    End If
End If

End Function

The following is a list of valid CSIDL values.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' CSIDL Constants of various folder names.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Public Const CSIDL_ADMINTOOLS As Long = &H30
    Public Const CSIDL_ALTSTARTUP As Long = &H1D
    Public Const CSIDL_APPDATA As Long = &H1A
    Public Const CSIDL_BITBUCKET As Long = &HA
    Public Const CSIDL_COMMON_ADMINTOOLS As Long = &H2F
    Public Const CSIDL_COMMON_ALTSTARTUP As Long = &H1E
    Public Const CSIDL_COMMON_APPDATA As Long = &H23
    Public Const CSIDL_COMMON_DESKTOPDIRECTORY As Long = &H19
    Public Const CSIDL_COMMON_DOCUMENTS As Long = &H2E
    Public Const CSIDL_COMMON_FAVORITES As Long = &H1F
    Public Const CSIDL_COMMON_PROGRAMS As Long = &H17
    Public Const CSIDL_COMMON_STARTMENU As Long = &H16
    Public Const CSIDL_COMMON_STARTUP As Long = &H18
    Public Const CSIDL_COMMON_TEMPLATES As Long = &H2D
    Public Const CSIDL_CONNECTIONS As Long = &H31
    Public Const CSIDL_CONTROLS As Long = &H3
    Public Const CSIDL_COOKIES As Long = &H21
    Public Const CSIDL_DESKTOP As Long = &H0
    Public Const CSIDL_DESKTOPDIRECTORY As Long = &H10
    Public Const CSIDL_DRIVES As Long = &H11
    Public Const CSIDL_FAVORITES As Long = &H6
    Public Const CSIDL_FLAG_CREATE As Long = &H8000
    Public Const CSIDL_FLAG_DONT_VERIFY As Long = &H4000
    Public Const CSIDL_FLAG_MASK As Long = &HFF00&
    Public Const CSIDL_FLAG_PFTI_TRACKTARGET As Long = CSIDL_FLAG_DONT_VERIFY
    Public Const CSIDL_FONTS As Long = &H14
    Public Const CSIDL_HISTORY As Long = &H22
    Public Const CSIDL_INTERNET As Long = &H1
    Public Const CSIDL_INTERNET_CACHE As Long = &H20
    Public Const CSIDL_LOCAL_APPDATA As Long = &H1C
    Public Const CSIDL_MYPICTURES As Long = &H27
    Public Const CSIDL_NETHOOD As Long = &H13
    Public Const CSIDL_NETWORK As Long = &H12
    Public Const CSIDL_PERSONAL As Long = &H5   ' My Documents
    Public Const CSIDL_MY_DOCUMENTS As Long = &H5
    Public Const CSIDL_PRINTERS As Long = &H4
    Public Const CSIDL_PRINTHOOD As Long = &H1B
    Public Const CSIDL_PROFILE As Long = &H28
    Public Const CSIDL_PROGRAM_FILES As Long = &H26
    Public Const CSIDL_PROGRAM_FILES_COMMON As Long = &H2B
    Public Const CSIDL_PROGRAM_FILES_COMMONX86 As Long = &H2C
    Public Const CSIDL_PROGRAM_FILESX86 As Long = &H2A
    Public Const CSIDL_PROGRAMS As Long = &H2
    Public Const CSIDL_RECENT As Long = &H8
    Public Const CSIDL_SENDTO As Long = &H9
    Public Const CSIDL_STARTMENU As Long = &HB
    Public Const CSIDL_STARTUP As Long = &H7
    Public Const CSIDL_SYSTEM As Long = &H25
    Public Const CSIDL_SYSTEMX86 As Long = &H29
    Public Const CSIDL_TEMPLATES As Long = &H15
    Public Const CSIDL_WINDOWS As Long = &H24

Using The Shell Controls Library

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

Using The FileDialog Method

You can use the following code to display the application's FileDialog to select as folder.

Function BrowseFolder(Title As String, _
        Optional InitialFolder As String = vbNullString, _
        Optional InitialView As Office.MsoFileDialogView = _
            msoFileDialogViewList) As String
    Dim V As Variant
    Dim InitFolder As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = Title
        .InitialView = InitialView
        If Len(InitialFolder) > 0 Then
            If Dir(InitialFolder, vbDirectory) <> vbNullString Then
                InitFolder = InitialFolder
                If Right(InitFolder, 1) <> "\" Then
                    InitFolder = InitFolder & "\"
                End If
                .InitialFileName = InitFolder
            End If
        End If
        .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 InitialFolder, which is optional, specifies the initial folder to which the dialog should open. The third parameter, also optional, InitialView 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.