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
- FileDialogs for Selecting Multiple Files (this blog)
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.
FileDialogs for Selecting Multiple Files
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.
The AllowMultiSelect Property
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
Picking Up on the Files Selected
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.
A Final Thought
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!
- Using File and Folder Dialog Boxes in VBA
- File and Folder Dialog Boxes
- FileDialogs for Selecting Multiple Files (this blog)