557 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
How to choose files and folders in VBA Part two of a three-part series of blogs |
---|
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.
This blog is part of a complete Excel VBA tutorial. Have a look also at our VBA training courses. |
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).
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.
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:
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.
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.
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.