Prompting for a Directory

If you need to get a filename, the simplest solution is to use the GetOpenFileName method, as I describe earlier. But if you need to get a directory name only (no file), you have two choices.

• A Windows API function. This method is a bit more complicated but displays a commonly used (and familiar) dialog box. A limitation is that you can't specify a default or starting directory.

• Excel's FileDialog object. This method is much easier to implement, and the dialog box resembles the standard Open dialog box. The FileDialog object was introduced in Excel 2002, so it won't work with earlier versions of Excel.

Both of these techniques are described in the sections that follow.

CD-ROM  

The companion CD-ROM contains a workbook that demonstrates both of these methods . The file is named  get directory.xlsm .

Using a Windows API function to select a directory

In this section, I present a function named GetDirectory that displays the dialog box shown in Figure 12-9 and returns a string that represents the selected directory. If the user clicks Cancel, the function returns an empty string.

Figure 12-9: Use an API function to display this dialog box.

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.

Following are the API declarations required at the beginning of the workbook module. This function also uses a custom data type, called BROWSEINFO .

 '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 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 

The GetDirectory function follows :

 Function GetDirectory(Optional Msg) As String     Dim bInfo As BROWSEINFO     Dim path As String     Dim r As Long,  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      = 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 

The simple procedure that follows demonstrates how to use the GetDirectory function in your code. Executing this procedure displays the dialog box. When the user clicks OK, the MsgBox function displays the full path of the selected directory. If the user clicks Cancel, the message box displays Canceled .

 Sub GetAFolder1()     Dim Msg As String     Dim UserFile As String     Msg = "Please select a location for the backup."     UserFile = GetDirectory(Msg)     If UserFile = "" Then         MsgBox "Canceled"     Else         MsgBox UserFile     End If End Sub 

Note  

Unfortunately, there is no easy way to specify a default or starting directory.

Using the FileDialog object to select a directory

If users of your application all use Excel 2002 or later, you might prefer to use a much simpler technique that uses the FileDialog object.

The following procedure displays a dialog box (see Figure 12-10) that allows the user to select a directory. The selected directory name (or Canceled ) is then displayed by using the MsgBox function.

Figure 12-10: Using the FileDialog object to select a directory.

 Sub GetAFolder2() '   For Excel 2002 and later     With Application.FileDialog(msoFileDialogFolderPicker)         .InitialFileName = Application.DefaultFilePath & "\"         .Title = "Please select a location for the backup"         .Show         If .SelectedItems.Count = 0 Then             MsgBox "Canceled"         Else             MsgBox .SelectedItems(1)         End If     End With End Sub 

The FileDialog object lets you specify the starting directory by specifying a value for the InitialFileName property. In this case, the code uses Excel's default file path as the starting directory.