How To: Return a list of filenames using GetOpenFilename
The GetOpenFilename Method has a MultiSelect parameter that lets you select and return an array of selected filenames. Here's an encapsulated function that calls the GetOpenFilename Method, opens the File Open Dialog to let the user select one or more files, and returns them as an array to the calling function.
As Variant()
' returns a list of selected filenames
On Error Resume Next
GetFilenames = _
Application.GetOpenFilename("Excel Files (*.xl*), *.xl*", , title, , True)
End Function
To call the GetFilenames Method, just assign the result to a Variant type, optionally passing a dialog box title, as follows:
Dim filenames() As Variant
Dim i As Long
Dim dummyString As String
filenames = GetFilenames
' check for empty array
On Error Resume Next
dummyString = filenames(1)
If Len(dummyString) = 0 Then
Exit Sub
End If
On Error GoTo 0
For i = LBound(filenames) To UBound(filenames)
Debug.Print filenames(i)
Next i
End Sub
This sample code simply prints the filenames to the Immediate Window in the VBA IDE, but you could do anything with the resulting array, for example call another function that opens or moves each file.
The code is clearly slanted towards Excel files, and it's written to work in any version of Excel (even 2007!). If you use it mostly for Excel files, but occasionally use it with other file types, you could generalize it like this:
Optional fileType As String = "*.xl*", _
Optional title As String = "Select Files") As Variant()
' returns a list of selected filenames
On Error Resume Next
GetFilenames = _
Application.GetOpenFilename("""" & fileTypeName & " (" & fileType & "), " & fileType & """", , title, , True)
End Function
Then to call the function you would write
or
