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