Working with GetOpenFileName

In Excel 2003 VBA, you can use GetOpenFileName to display the standard Open dialog box. If you set the MultiSelect parameter to True it is supposed to return an array of file names from the user. It returns False if the dialog is cancelled.

One standard way of calling this function is along the following lines:

Dim fileNameList
Dim fileNum As Integer

fileNameList = Application.GetOpenFilename( _
      "Text Files (*.txt), *.txt", _
      1, "Select One Or More Files To Open", , True)

If fileNameList <> False Then
  For fileNum = 1 To UBound(fileNameList)
    Call ProcessFile(fileNameList(fileNum))
  Next fileNum
End If

However — even when MultiSelect is True — GetOpenFileName doesn’t always correctly return a variant array; sometimes it returns just a single file name. This causes a Type Mismatch error on the subsequent call to UBound.

The following alternative code can be used as a workaround. It tests whether the function has returned a string or an array and acts appropriately:

Dim fileNameList
Dim fileName As String
Dim fileNum As Integer

fileNameList = Application.GetOpenFilename( _
      "Text Files (*.txt), *.txt", _
      1, "Select One Or More Files To Open", , True)

If fileNameList <> False Then
  If TypeName(fileNameList) = "String" Then
    fileName = fileNameList
    Call ProcessFile(fileName)
  ElseIf TypeName(fileNameList) = "Variant()" Then
    For fileNum = 1 To UBound(fileNameList)
      fileName = fileNameList(fileNum)
      Call ProcessFile(fileName)
    Next fileNum
  End If
End If