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
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
This blog is part of a complete Excel VBA tutorial. Have a look also at our VBA training courses. |
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:
Sub OpenSeveralFiles()
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
'initial folder
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
Workbooks.Open fd.SelectedItems(i)
Next i
End If
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!
End If
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 |
---|
|
Some other pages relevant to the above blogs include:
From: | GDSever |
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.
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.