This page has 0 threads | Add post

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

How to choose files and folders in VBA Part two 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 (this blog) FileDialogs for Selecting Multiple Files 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 two 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 (this blog)
FileDialogs for Selecting Multiple Files

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

File and Folder Dialog Boxes

The Types of File and Folder Dialog Box

There are four possible choices you can make when displaying a file or folder dialog box:

Choice	What it does
msoFileDialogFilePicker	Lets users select one or more files
msoFileDialogFolderPicker	Lets users select a folder path
msoFileDialogOpen	Lets users select one or more files to open
msoFileDialogSaveAs	Lets users select a single file to save

There are actually really only 2 choices. The first, third and fourth options above all display a dialog box for choosing a file; the second displays a dialog box for choosing a folder. In particular, note that just because you're using an msoFileDialogOpen dialog box, for example, doesn't mean that Excel will then open any file you pick (it's still up to you to do that in code).

Displaying a Vanilla File Dialog Box

The basic code to create and display a file dialog box involves applying the FileDialog method to the Application object:

Sub ChooseFile()

Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

'get the number of the button chosen

Dim FileChosen As Integer

FileChosen = fd.Show

If FileChosen <> -1 Then

'didn't choose anything (clicked on CANCEL)

MsgBox "You chose cancel"

Else

'display name and path of file chosen

MsgBox fd.SelectedItems(1)

End If

End Sub

This would display a basic dialog box. If a user then chose a file, the macro would display its name:

The first element of the SelectedItems array shows the name of the one and only file chosen.

Notice how VBA inconsistently denotes the first item in the array of selected files with the number 1, not 0.

Making a File or Folder Dialog Box Prettier

Now that we've created a basic dialog box, let's look at how to tart it up a bit (a good UK English expression that - wonder how well it travels ...). The diagram below shows that you can change:

The caption of the dialog box (here to Example of choosing file)
The initial folder selected (here to c:\Wise Owl\)
The view of your files (this one is msoFileDialogViewSmallIcons)
The filter used (here we're just looking for Excel macro workbooks)
The text on the Open button (here it's Choose owly file)

Here's the dialog box:

A prettified file dialog box!

Here is the code which would make this work, with comments to explain the various properties set:

Sub ChooseFile()

Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

'the number of the button chosen

Dim FileChosen As Integer

FileChosen = fd.Show

'1) To set the caption of the dialog box,

' set the Title property

fd.Title = "Example of choosing file"

'2) Set the oddly named InitialFileName property to

' determine the initial folder selected

fd.InitialFileName = "c:\wise owl\"

'3) Set the InitialView property to control how your files

' appear on screen (as a list, icons, etc.)

fd.InitialView = msoFileDialogViewSmallIcons

'4) To set the filters (you can have as many as you like)

' first clear any existing ones, then add them one by one

fd.Filters.Clear

fd.Filters.Add "Excel macros", "*.xlsm"

' if there's more than one filter, you can control which

' one is selected by default

fd.FilterIndex = 1

'5) Set the ButtonName property to control the text on

' the OK button (the ampersand means the following

' letter is underlined and choosable with the ALT key)

fd.ButtonName = "Choose &owly file"

If FileChosen <> -1 Then

'didn't choose anything (clicked on CANCEL)

MsgBox "You chose cancel"

Else

'display name and path of file chosen

MsgBox fd.SelectedItems(1)

End If

End Sub

The button name won't appear - oddly - until you've clicked on a file first.

A FileDialog Example - Choosing a File to Open

The following macro would allow you to choose an Excel workbook to open, and then open it:

Sub OpenWorkbook()

Dim fd As FileDialog

Dim FileName As String

Set fd = Application.FileDialog(msoFileDialogOpen)

'the number of the button chosen

Dim FileChosen As Integer

FileChosen = fd.Show

fd.Title = "Choose workbook"

fd.InitialFileName = "c:\wise owl\"

fd.InitialView = msoFileDialogViewList

'show Excel workbooks and macro workbooks

fd.Filters.Clear

fd.Filters.Add "Excel workbooks", "*.xlsx"

fd.Filters.Add "Excel macros", "*.xlsm"

fd.FilterIndex = 1

fd.ButtonName = "Choose this file"

If FileChosen <> -1 Then

'didn't choose anything (clicked on CANCEL)

MsgBox "No file opened"

Else

'get file, and open it (NAME property

'includes path, which we need)

FileName = fd.SelectedItems(1)

Workbooks.Open (FileName)

End If

End Sub

Notice that although here we used the msoFileDialogOpen dialog type to choose a workbook, this didn't automatically open it!

That's most of the story on FileDialogs - in the final part of this blog, we'll consider how to work with multiple files.

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

Some other pages relevant to the above blogs include:

This blog has 0 threads

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

Google Sheets

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

BLOGS BY AUTHOR

File and Folder Dialog Boxes

The Types of File and Folder Dialog Box

Displaying a Vanilla File Dialog Box

Making a File or Folder Dialog Box Prettier

A FileDialog Example - Choosing a File to Open

Head office

London

Manchester