Selecting a Directory
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 you can prompt the user to select a directory by using the new FileDialog object. The advantage is that you can specify a starting directory.