Excel VBA select multiple files in dialog

This Excel VBA tutorial explains how to select multiple files in dialog using Application.GetOpenFileName.

Excel VBA select file in dialog

In many applications, you would see a “browse” button where you can select files for the application to process.

Excel insert file 06

In Excel VBA, you can create a Macro to open the browse dialog. After you select the target file, the file path is stored in a VBA Array, then you can loop through the array to get items, and then do whatever you like with the file.

There are different ways you can use to create a dialog to select file, for example, Application.FileDialog, Application.GetOpenFileName.

In this post, I will introduce Application.GetOpenFileName.

Syntax of Application.GetOpenFileName

Application.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect)
FileFilter Optional Variant. A string specifying file filtering criteria.
FilterIndex Optional Variant. Specifies the title of the dialog box. If this argument is omitted, the title is “Open.”
ButtonText Optional Variant. Macintosh only.
MultiSelect Optional Variant. True to allow multiple file names to be selected. False to allow only one file name to be selected. The default value is False

Example – select multiple files in dialog

The below Macro allows you to select any file types and allow multiple selection.

Public Sub test()
    fpathArray = Application.GetOpenFilename(FileFilter:="All Files,*.*", Title:="Select file", MultiSelect:=True)
End Sub

Suppose we run the Macro and select the below three files, click on Open. Then the three file paths are stored in fpathArray.

excel-select-multiple-files-01

To verify, add a msgbox to see what has been stored in the fpathArray.

Public Sub test2()
    fpathArray = Application.GetOpenFilename(FileFilter:="All Files,*.*", Title:="Select file", MultiSelect:=True)
    MsgBox (fpathArray(1) & vbCrLf & fpathArray(2) & vbCrLf & fpathArray(3))
End Sub

Run the above Macro and then select the three files, we can see the file paths are stored in Array. Note that the first item is stored in fpathArray(1) instead of fpathArray(0).

excel-select-multiple-files-02

You can also write a For Loop to loop through the Array items.

Public Sub test3()
    fpathArray = Application.GetOpenFilename(FileFilter:="All Files,*.*", Title:="Select file", MultiSelect:=True)
    For i = 1 To UBound(fpath)
       MsgBox (fpathArray(i))
    Next i
End Sub

For more examples, you may refer to my previous post regarding insert file objects.

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *