Microsoft Office Tutorials and References
In Depth Information
Prompting for a Directory
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 can use Excel’s
FileDialog object.
The following procedure displays a dialog box (see Figure 12-9) that allows the user to select a
directory. The selected directory name (or Canceled ) is then displayed by using the MsgBox function.
Figure 12-9: Using the FileDialog object to select a directory.
Sub GetAFolder ()
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & “\”
.Title = “Select a location for the backup”
If .SelectedItems.Count = 0 Then
MsgBox “Canceled”
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 example, the code uses Excel’s default file path as the
starting directory.
Displaying Excel’s Built-In Dialog Boxes
Code that you write in VBA can execute many of Excel’s Ribbon commands. And, if the
command normally leads to a dialog box, your code can “make choices” in the dialog box (although
the dialog box itself isn’t displayed). For example, the following VBA statement is equivalent to
Search JabSto ::

Custom Search