Calling FileOpen dialog in VBA does not allow opening of multiple files

Article contributed by Ibby

If you call the FileOpen dialog using the following code, you will receive an error if you try to open multiple files using the dialog:


The workaround is to show the dialog by executing the appropriate toolbar button:

CommandBars.FindControl(ID:=23, Visible:=False).Execute

Setting Visible:=False (which is the default anyway) will ensure the button will be executed even if it's invisible (i.e.: even if the user has doesn't have it displayed in their toolbar).

If you also want to find out which files were, in fact, opened by the user, you could use the following code which was originally posted in the newsgroups by Christa Siebe:

Sub LoadSeveralFiles()

Dim OpenDlg As CommandBarControl
Dim oldDocs() As String
Dim i As Long, j As Long
Dim IsNewDoc As Boolean

ReDim oldDocs(1 To Application.Documents.Count)

'because documents start with 1 as well...
For j = 1 To Application.Documents.Count
    oldDocs(j) = Application.Documents(j).FullName

Set OpenDlg = CommandBars.FindControl(ID:=23)

If UBound(oldDocs) < Application.Documents.Count Then
    MsgBox "one or more files are opened!"
    For i = 1 To Application.Documents.Count
        IsNewDoc = True
        For j = 1 To UBound(oldDocs)
            If Application.Documents(i).FullName = oldDocs(j) Then
                IsNewDoc = False
                Exit For
            End If
        Next j
        If IsNewDoc Then
            MsgBox "Doc '" & Application.Documents(i).FullName & "' is a new one..."
        End If
    Next i
End If

End Sub

Click to view Terms of Use page

Click to view Disclaimer page