BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
To make it easier for users of your VBA systems to choose files, you can show FileDialogs on screen. This blog explains what these are, and how to customise them.
- Using File and Folder Dialog Boxes in VBA
- File and Folder Dialog Boxes (this blog)
- FileDialogs for Selecting Multiple Files
This blog is part of a complete Excel VBA tutorial. Have a look also at our VBA training courses.
Posted by Andy Brown on 13 January 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
File and Folder Dialog Boxes
The Types of File and Folder Dialog Box
There are four possible choices you can make when displaying a file or folder dialog box:
| Choice | What it does |
|---|---|
| msoFileDialogFilePicker | Lets users select one or more files |
| msoFileDialogFolderPicker | Lets users select a folder path |
| msoFileDialogOpen | Lets users select one or more files to open |
| msoFileDialogSaveAs | Lets users select a single file to save |
There are actually really only 2 choices. The first, third and fourth options above all display a dialog box for choosing a file; the second displays a dialog box for choosing a folder. In particular, note that just because you're using an msoFileDialogOpen dialog box, for example, doesn't mean that Excel will then open any file you pick (it's still up to you to do that in code).
Displaying a Vanilla File Dialog Box
The basic code to create and display a file dialog box involves applying the FileDialog method to the Application object:
Sub ChooseFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'get the number of the button chosen
Dim FileChosen As Integer
FileChosen = fd.Show
If FileChosen <> -1 Then
'didn't choose anything (clicked on CANCEL)
MsgBox "You chose cancel"
Else
'display name and path of file chosen
MsgBox fd.SelectedItems(1)
End If
End Sub
This would display a basic dialog box. If a user then chose a file, the macro would display its name:
The first element of the SelectedItems array shows the name of the one and only file chosen.
Notice how VBA inconsistently denotes the first item in the array of selected files with the number 1, not 0.
Making a File or Folder Dialog Box Prettier
Now that we've created a basic dialog box, let's look at how to tart it up a bit (a good UK English expression that - wonder how well it travels ...). The diagram below shows that you can change:
- The caption of the dialog box (here to Example of choosing file)
- The initial folder selected (here to c:\Wise Owl\)
- The view of your files (this one is msoFileDialogViewSmallIcons)
- The filter used (here we're just looking for Excel macro workbooks)
- The text on the Open button (here it's Choose owly file)
Here's the dialog box:
A prettified file dialog box!
Here is the code which would make this work, with comments to explain the various properties set:
Sub ChooseFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'the number of the button chosen
Dim FileChosen As Integer
FileChosen = fd.Show
'1) To set the caption of the dialog box,
' set the Title property
fd.Title = "Example of choosing file"
'2) Set the oddly named InitialFileName property to
' determine the initial folder selected
fd.InitialFileName = "c:\wise owl\"
'3) Set the InitialView property to control how your files
' appear on screen (as a list, icons, etc.)
fd.InitialView = msoFileDialogViewSmallIcons
'4) To set the filters (you can have as many as you like)
' first clear any existing ones, then add them one by one
fd.Filters.Clear
fd.Filters.Add "Excel macros", "*.xlsm"
' if there's more than one filter, you can control which
' one is selected by default
fd.FilterIndex = 1
'5) Set the ButtonName property to control the text on
' the OK button (the ampersand means the following
' letter is underlined and choosable with the ALT key)
fd.ButtonName = "Choose &owly file"
If FileChosen <> -1 Then
'didn't choose anything (clicked on CANCEL)
MsgBox "You chose cancel"
Else
'display name and path of file chosen
MsgBox fd.SelectedItems(1)
End If
End Sub
The button name won't appear - oddly - until you've clicked on a file first.
A FileDialog Example - Choosing a File to Open
The following macro would allow you to choose an Excel workbook to open, and then open it:
Sub OpenWorkbook()
Dim fd As FileDialog
Dim FileName As String
Set fd = Application.FileDialog(msoFileDialogOpen)
'the number of the button chosen
Dim FileChosen As Integer
FileChosen = fd.Show
fd.Title = "Choose workbook"
fd.InitialFileName = "c:\wise owl\"
fd.InitialView = msoFileDialogViewList
'show Excel workbooks and macro workbooks
fd.Filters.Clear
fd.Filters.Add "Excel workbooks", "*.xlsx"
fd.Filters.Add "Excel macros", "*.xlsm"
fd.FilterIndex = 1
fd.ButtonName = "Choose this file"
If FileChosen <> -1 Then
'didn't choose anything (clicked on CANCEL)
MsgBox "No file opened"
Else
'get file, and open it (NAME property
'includes path, which we need)
FileName = fd.SelectedItems(1)
Workbooks.Open (FileName)
End If
End Sub
Notice that although here we used the msoFileDialogOpen dialog type to choose a workbook, this didn't automatically open it!
That's most of the story on FileDialogs - in the final part of this blog, we'll consider how to work with multiple files.
- Using File and Folder Dialog Boxes in VBA
- File and Folder Dialog Boxes (this blog)
- FileDialogs for Selecting Multiple Files