If you found this blog useful and you’d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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

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.

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.

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

This blog has 0 threads Add post