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.

  1. Using File and Folder Dialog Boxes in VBA
  2. File and Folder Dialog Boxes
  3. 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:

Multiple files selected

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!

Fonts dialog box

But I'm not quite sure where you'd go from here ...

Food for thought! 

 

  1. Using File and Folder Dialog Boxes in VBA
  2. File and Folder Dialog Boxes
  3. FileDialogs for Selecting Multiple Files (this blog)
This blog has 0 threads Add post