Excel VBA, using FileDialog to open multiple workbooks and reference them

Question

I am currently using to following code to prompt the user for a workbook, open it, get some information from it and then close it. at the moment, I address the opened workbook by using the workbooks collection with and index ("woorkbooks(2)"). Now I need to open two workbooks, and my problem is that I wouldn't know which of the workbooks will be indexed as 2 and which will be indexed as 3. So, I figured there must be a way to get a reference to each workbook.

Function openfile() As Boolean

Dim fd As FileDialog
Dim file_was_chosen As Boolean

Set fd = Application.FileDialog(msoFileDialogOpen)

With fd
    .Filters.Clear
    .Filters.Add "Excel File", "*.xl*"
End With

file_was_chosen = fd.Show

If Not file_was_chosen Then
    MsgBox "You didn't select a file"
    openfile = False
    Exit Function
End If

fd.Execute
openfile = True

End Function

Now I've seen some solutions to this problem involving getting the full path of each workbook, but I'd prefer avoid using the full path since it contains words in different language (and the name of the workbook appears with question marks). Moreover, I'd prefer a solution in which the user is promped only once for 2 files and not twice.

score 2 · Accepted Answer · answered Mar 13 '17 at 03:46

This version gives the user a single dialog. Enjoy. And whoever downvoted my other answer, please add a comment to that explaining what you so disliked about it that it required a downvote.

Function openfile() As Variant
    Dim aOpen(2) As String, itm As Variant, cnt As Long, lAsk As Long
    Dim fd As FileDialog
    Dim file_was_chosen As Boolean

    Set fd = Application.FileDialog(msoFileDialogOpen)

    With fd
        .Filters.Clear
        .Filters.Add "Excel File", "*.xl*"
    End With

    Do
        file_was_chosen = fd.Show
        If Not file_was_chosen Or fd.SelectedItems.Count > 2 Then
            lAsk = MsgBox("You didn't select one or two files, try again?", vbQuestion + vbYesNo, "File count mismatch")
            If lAsk = vbNo Then
                openfile = aOpen
                Exit Function
            End If
        End If
    Loop While fd.SelectedItems.Count < 1 Or fd.SelectedItems.Count > 2

    cnt = 0
    For Each itm In fd.SelectedItems
        aOpen(cnt) = itm
        cnt = cnt + 1
    Next
    openfile = aOpen
    fd.Execute
End Function

Sub test()
    Dim vRslt As Variant
    Dim wkb As Excel.Workbook, wkb1 As Excel.Workbook, wkb2 As Excel.Workbook

    vRslt = openfile
    For Each wkb In Application.Workbooks
        If wkb.Path & "\" & wkb.Name = vRslt(0) Then Set wkb1 = wkb
        If wkb.Path & "\" & wkb.Name = vRslt(1) Then Set wkb2 = wkb
    Next

    If vRslt(0) = "" Then ' no files
        MsgBox "No files opened so nothing happens..."
    ElseIf vRslt(1) = "" Then ' one file was opened
        MsgBox "One file so do whatever you want for one file"
    Else ' two files were opened
        MsgBox "Two files so do whatever you want for two files"
    End If        
End Sub

Thank you very much, that's exactly what I needed! (and thank for you being consistent with my variables, it was very helpfull) — Sergey, Mar 13 '17 at 09:14

Winterknell · Answer 2 · 2017-03-13T01:17:50.257

Working with your existing openfile function, change the return from Boolean to Excel.Workbook. If they don't open a workbook you set it to Nothing instead of false, otherwise you set it to the workbook reference of the file you just opened (You'll need to modify openfile to get that reference). You then just call it twice and set a workbook reference for each call that is not Nothing.

Example code below is written freeform and is untested - it's really just glorified pseudocode - but should point you the right general direction.

sub test
    dim lAsk as long
    dim wkb1 as excel.workbook
    dim wkb2 as excel.workbook
    do
        if wkb1 is Nothing then
            set wkb1 = openfile
            if wkb1 is Nothing then
                lAsk = msgbox("you didn't select a first file, try again?",vbyesno,"No file selected")
                if lAsk = vbNo then exit do
            end if
        elseif wkb2 is Nothing then
            set wkb2 = openfile
            if wkb2 is Nothing then
                lAsk = msgbox("you didn't select a second file, try again?",vbyesno,"No file selected")
                if lAsk = vbNo then exit do
            end if
        end if
    loop while wkb1 is Nothing or wkb2 is Nothing

    ' do whatever with wkb1 and wkb2 here

end sub

Edited to add:

Here's a very basic shape for your revised openfile function. Again, untested but I've modified it from one of my own procs so it should work

Function openfile() As Excel.Workbook
    Dim sFilter As String
    Dim sTitle As String
    Dim vFileName As Variant

    sFilter = "Excel Files (*.xl*), *.xl*, CSV Files (*.csv), *.csv, All Files (*.*), *.*"
    sTitle = "Select file to process"

    vFileName = Application.GetOpenFilename(filefilter:=sFilter, Title:=sTitle)
    If vFileName = False Then
        Set openfile = Nothing
    Else
        Set openfile = Workbooks.Open(Filename:=vFileName)
    End If
End Function

Excel VBA, using FileDialog to open multiple workbooks and reference them

2 Answers2