560 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
How to choose files and folders in VBA
Part three 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.
Sometimes you'll want to display a dialog box and allow a user to choose a number of files at the same time. To do this you need to know how to set the AllowMultiSelect property of a dialog box, and deal with the files returned.
You can tell Excel to allow multiple file selection as follows:
Dim fd As FileDialog
Dim FileChosen As Integer
Dim FileName As String
Dim i As Integer
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'use the standard title and filters, but change the
fd.InitialFileName = "c:\wise owl\"
fd.InitialView = msoFileDialogViewList
'allow multiple file selection
fd.AllowMultiSelect = True
This automatically means that you can use the CTRL key, SHIFT key and mouse to select multiple files. For example:
Here we've selected 2 files to open
Having chosen some files, you can loop over the SelectedItems array processing each item in it:
FileChosen = fd.Show
If FileChosen = -1 Then
'open each of the files chosen
For i = 1 To fd.SelectedItems.Count
Once again, notice how strange it is that the first item in the array is 1, not 0 as it would be normally.
This blog has shown how to use the file and folder dialog boxes, but you can also get at any of the dialog boxes built into Excel. For example, this code:
Dim fd As Dialog
Dim ButtonChosen As Integer
Set fd = Application.Dialogs(xlDialogFont)
ButtonChosen = fd.Show
If ButtonChosen = -1 Then
'not quite sure what you'd do now!
would bring up this dialog box!
But I'm not quite sure where you'd go from here ...
Food for thought!
|Parts of this blog|
|When:||14 Jul 23 at 19:19|
Regarding the comment for this article that says "Once again, notice how strange it is that the first item in the array is 1, not 0 as it would be normally.", I'd like to chime in.
The SelectedItems property of the FileDialog object returns a Collection object, not an array. While arrays typically start with an index of zero, that is not the case for collections - the first object in a collection (almost) always has an index of 1. This assertion is based on the SelectedItems property also having a method of .Item() which is really only seen for collections. There are many other collection objects within Office VBA like this - like Workbooks, Worksheets, Documents, etc. When I see the .Item() property, I default iteration loops to start from 1 instead of 0 out of habit.
|When:||16 Jul 23 at 10:26|
A good point well made! Thanks.
25 Aytoun Street