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.

Function GetFilenames(Optional title As String = "Select Files") _
    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:

Sub TestGetFilenames()

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:

Function GetFilenames(Optional fileTypeName As String = "Excel Files", _
    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

filenames = GetFilenames("Java Files", "*.java") ' to return Java files

filenames = GetFilenames() ' for Excel files (default)