CODEWITHSUNDEEP
vbaexcel
Arthur Le Calvez
Arthur Le Calvez

Reputation: 433

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

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.

Upvotes: 2

Views: 256

Answers (3)

Vityata
Vityata

Reputation: 43595

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:

If there is an opened workbook, then close it:

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

Upvotes: 2

Vincent G
Vincent G

Reputation: 3188

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

Upvotes: 3

Kubie
Kubie

Reputation: 1571

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

Upvotes: 3

Related Questions