When two workbooks are opened in visual basic, workbook variables do not get assigned correctly

Question

When running the code below, I get different results depending on which workbooks are open. The sub is in a module associated with Master Sheet.xlsm

1. If just the Master Sheet.xlsm open then the code runs correctly, i.e. the Message Boxes say (where comma separates the first and second message box): Master Sheet, transferred cases 03-09-18

2. If both Master Sheet.xlsm and transferred cases 03-09-18.xlsx are open but transferred cases 03-09-18.xlsx was open second then the message boxes say: transferred cases 03-09-18, transferred cases 03-09-18

3. If both Master Sheet.xlsm and transferred cases 03-09-18.xlsx are open but Master Sheet.xlsm was open second then the message boxes say: Master Sheet, Master Sheet

---

Sub foo()
    Dim x As Workbook
    Dim y As Workbook

   '## Open both workbooks first:
   Set x = Workbooks.Open("C:\Users\owner\Documents\ExelatecOutput\Master Sheet.xlsm")
   Set y = Workbooks.Open("C:\Users\owner\Documents\ExelatecOutput\transferred cases 03-09-18.xlsx")

   'Now, copy what you want from x:
   MsgBox x.Name
   MsgBox y.Name

End Sub

Why do the variables x and y not get assigned correctly.

Answer 1

Workbooks.Open always return the last opened file (even if it is not the one passed in parameter). This is either bad documentation or bug in excel IMO.

You don't need to check if the file is opened, since opening an already open file does not raise an error, but you need to set the variable later:

Workbooks.Open "C:\Users\owner\Documents\ExelatecOutput\Master Sheet.xlsm": Set x = Workbooks("Master Sheet.xlsm") ' or Set x = ActiveWorkbook since Open will activate it
Workbooks.Open "C:\Users\owner\Documents\ExelatecOutput\transferred cases 03-09-18.xlsx": Set y = Workbooks("transferred cases 03-09-18.xlsx") ' or Set y = ActiveWorkbook since Open will activate it

Answer 2

Just a note, you can check if the workbooks are already open with a function like this where you pass a workbook name.

Public Function BookOpen(strBookName As String) As Boolean

    Dim oBk As Workbook
    On Error Resume Next
    Set oBk = Workbooks(strBookName)
    On Error GoTo 0
    If oBk Is Nothing Then
        BookOpen = False
    Else
        BookOpen = True
    End If

End Function

If it returns true, you can set x = Workbooks("your workbook name")