This page has 1 thread | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. 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.

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.

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

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!

Parts of this blog
Using File and Folder Dialog Boxes in VBA File and Folder Dialog Boxes FileDialogs for Selecting Multiple Files (this blog)

Some other pages relevant to the above blogs include:

This blog has 1 thread

Add a new post

From:	GDSever
When:	14 Jul 23 at 19:19

Reply to GDSever

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.

From:	Andy B
When:	16 Jul 23 at 10:26

Reply to Andy B

A good point well made! Thanks.

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC