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

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
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
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.

Before running the code, none of the Workbooks should be opened. — Vityata, Sep 04 '18 at 14:30
Workbooks.Open always return the last **opened** file (even if it is not the one passed in parameter) — Vincent G, Sep 04 '18 at 14:34
@VincentG - this is quite strange. Can you put some code to prove it? Or documentation. I am reading here - https://learn.microsoft.com/en-us/office/vba/api/Excel.Workbooks.Open — Vityata, Sep 04 '18 at 14:41
@Vityata I read the same documentation as you. I think is is either a bug or bad documentation. To try it: Run the OP code once, then manually open a file and rerun the code: The displayed names will be the manually opened file. — Vincent G, Sep 04 '18 at 14:48
I can reproduce Arthur's problem. If I open two workbooks with his code and both workbooks are closed everything is fine. If I re-run the code with both workbooks open then x and y point to oneworkbook. So, it seems Wokbooks.open just always point to one workbook only in this case. And it seems to dependant on the order how I opend the workbook. I would agree it's good practice to check if a workbook is already open but anyway IMO it seems to be a strange behaviour of Workbooks.Open. — Storax, Sep 04 '18 at 14:49
I really hope that this "feature" was "introduced" with Excel 2016 and the way the Excel instance was made there. @Comintern - it is replicable, obviously `Workbooks.Open()` gives whatever it can get from the `Workbooks` collection, if the workbook is already opened. Even the Workbook, in which the code is. — Vityata, Sep 04 '18 at 15:06
Can anyone check this with Excel 2013 or earlier to confirm whether this "feature" is present there as well? — Vityata, Sep 04 '18 at 15:21

score 3 · Answer 1 · answered Sep 04 '18 at 14:31

3

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")

answered Sep 04 '18 at 14:31

Kubie

1,551
3
12
23

1

Quite the same as this one - https://stackoverflow.com/a/9374481/5448626 – Vityata Sep 04 '18 at 14:39
The problem I've found with this method is it only works on the file name and not the file path as well - pass it a file path & name it will say it's closed, pass it just a name it will say a file with that name is open - but is it in the right path as well? – Darren Bartrup-Cook Sep 04 '18 at 14:53
The answer by SiddharthRout in the link from @Vityata is the best approach to take to cover all bases. – Scott Holtzman Sep 04 '18 at 14:54
@ScottHoltzman You mean the one in the link in his answer I take it - as in the `Open FullFilePath For Input Lock Read As #ff` answer. – Darren Bartrup-Cook Sep 04 '18 at 14:58
@DarrenBartrup-Cook - Yes – Scott Holtzman Sep 04 '18 at 14:59

score 3 · Accepted Answer · answered Sep 04 '18 at 14:49

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

Vityata · Answer 3 · 2018-09-04T15:16:20.927

When assigning to a Workbook variable with Workbooks.Open() it is implied that the workbook, which is to be opened is closed. Otherwise, it takes either the last opened workbook with Workbooks.Open() or the workbook where the code is, if all the workbooks are opened already.

Thus, make sure that you close them before trying to open the workbooks. You need to do the following two actions before opening:

Detect whether Excel workbook is already open

If there is an opened workbook, then close it:

Closing Excel Application using VBA

Another faster option is to assign the variable explicitly, as mentioned by Vincent G, which is faster, because you are not going to close an already opened Excel file:

Sub TestMe()

    Dim x As Workbook
    Dim y As Workbook
    Dim xPath As String: xPath = "C:\Book1.xlsx"
    Dim yPath As String: yPath = "C:\Book2.xlsx"

    Workbooks.Open xPath
    Set x = Workbooks(Split(xPath, "\")(UBound(Split(xPath, "\"))))
    Workbooks.Open yPath
    Set y = Workbooks(Split(yPath, "\")(UBound(Split(yPath, "\"))))

    Debug.Print x.Name
    Debug.Print y.Name

End Sub

The part of the code Split(xPath, "\")(UBound(Split(xPath, "\"))) takes the last element of the splitted array by \.

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

3 Answers3

Linked