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