Selecting A Directory

Category: UserForms | [Item URL]

If your Excel application needs to prompt the user for a directory, you may have been surprised to discover that there is no direct way to do this. You can use the GetOpenFileName method to display a dialog that prompts for a file name, but there is no way to display a dialog box that shows only directories.

In this document I present a function (named GetDirectory) that displays the dialog box shown below, and returns a string that represents the selected directory. If the user clicks cancel, the function returns an empty string.

The GetDirectory function takes one argument, which is optional. This argument is a string that will be displayed in the dialog box. If the argument is omitted, the dialog box displays Select a folder as the message.

The GetDirectory function

The GetDirectory function is listed below. To use this function, copy the text and paste it to a VBA module.

Option Explicit
Public Type BROWSEINFO
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type

'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
  Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) _
  As Long

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

Sub Test()
    Dim Msg As String
    Msg = "Please select a location for the backup."

    MsgBox GetDirectory(Msg)
End Sub

Function GetDirectory(Optional Msg) As String
    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim r As Long, x As Long, pos As Integer
 
'   Root folder = Desktop
    bInfo.pidlRoot = 0&

'   Title in the dialog
    If IsMissing(Msg) Then
        bInfo.lpszTitle = "Select a folder."
    Else
        bInfo.lpszTitle = Msg
    End If
    
'   Type of directory to return
    bInfo.ulFlags = &H1

'   Display the dialog
    x = SHBrowseForFolder(bInfo)
    
'   Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal path)
    If r Then
        pos = InStr(path, Chr$(0))
        GetDirectory = Left(path, pos - 1)
    Else
        GetDirectory = ""
    End If
End Function

Using the function

The simple subroutine listed below demonstrates how to use the GetDirectory function in your code. Executing this subroutine displays the dialog box. When the user clicks OK or Cancel, the MsgBox function displays the full path of the selected directory.

Sub Test()
    Dim Msg As String
    Msg = "Please select a location for the backup."
    MsgBox GetDirectory(Msg)
End Sub

Specifying the starting directory

If you would like to be able to specify the starting directory, things get a lot more complicated.

NOTE: If you use Excel 2002 or later you can prompt the user to select a directory by using the FileDialog object. The advantage is that you can specify a starting directory.