1

I have code in workbook A that opens and does stuff to workbook B. Code runs fine when workbook A and B are the only excel files open (or if workbook A is the only file open). However, if I open up any additional workbook (call it workbook C), the macro does not run correctly. It doesn't cause an error message, it just runs to completion without doing any of the "stuff" it's supposed to do (the stuff is basically finding things in workbook B and pasting them into workbook A).

FWIW, I have done the following simple experiment:

  1. Open all 3 workbooks (A, B, & C)
  2. select workbook C so that it is active and the front window
  3. run the code workbookB.sheet1.activate (this is not verbatim, I know this code as written will fail)

When I do the above test, it doesn't even make workbook B the active workbook. Again, it doesn't cause excel to throw an error message, it just runs and leave workbook C as the active workbook.

Edit: I've tested more, the code below should change the value of a cell in workbook B, but instead is putting the value into workbook C. I am very confused, since workbook C is not referenced in any way (the module is in workbook A)

Sub test()
    Dim wb As Workbook
    Set wb = Workbooks.Open("U:\workbookB.xlsx")
    wb.Worksheets("ED").Range("Z1").Value = "TEST"
End Sub

Edit 2: The issue was occurring when Workbook A and B had been open for several hours, and Workbook C was recently opened. I closed workbook B then re-ran the code and it is working correctly. This leading me to believe that there IS some sort of issue with multiple instances of excel opening. While this is hopefully low-risk, I am still curious if anyone has some way I could code around it as a precaution? Thanks!

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
z3115
  • 39
  • 7
  • 1
    Normally you don't need to `Activate`. Fully qualifying which workbook and worksheet you are working with is what you need. – BigBen Jun 26 '19 at 20:46
  • BigBen - please see update. I know "activate" is not necessary, I was just using it as a simple way to troubleshoot - I could replicate the issue with a different action besides "activate"..after closing/reopening and having that fix it, I am wondering if it is a bug with having multiple excel instances open – z3115 Jun 26 '19 at 20:50
  • Set a Watch on "wb" and check the FullPath to confirm that it is indeed the file you are expecting it to be. – Frank Ball Jun 26 '19 at 20:54
  • Frank - that seems helpful, how exactly do I "set a watch" on a variable? thank you! – z3115 Jun 26 '19 at 20:57
  • in your code before `end sub` type `debug.print wb.path` – alowflyingpig Jun 26 '19 at 21:15
  • Title is a bit misleading - multiple "instances" typically refers to having more than one instance of the Excel *application* open (ie two separate Excel instances, each with their own set of open workbooks). – Tim Williams Jun 26 '19 at 21:21

2 Answers2

3

There is a subtle bug (I hesitate to call it that, but that's what it looks like) you need to watch out for.

If the workbook you're trying to open via code is already open then occasionally you will see some unexpected behavior (such as the return value from Workbooks.Open() being assigned to ThisWorkbook instead of the file you expect).

For example the code below runs in "Tester.xlsm" and is opening "EmptyTest.xlsx", but if that file is already open, the Workbooks.Open call fails to correctly assign the wb variable, and it ends up pointing at "Tester.xlsm". That can cause problems.

To replicate,

  • open Excel
  • open "EmptyTest.xlsx"
  • open "Tester.xlsm"
  • run "Tester" sub

Test code:

Sub Tester()

    Dim wb As Workbook

    'with "EmptyTest" already open
    Set wb = Workbooks.Open("C:\Tester\EmptyTest.xlsx")
    Debug.Print wb.Name '>> Tester.xlsm  -  oops!

    'close"EmptyTest" before proceeding
    Workbooks("EmptyTest.xlsx").Close False

    'with  "EmptyTest" closed
    Set wb = Workbooks.Open("C:\Tester\EmptyTest.xlsx")
    Debug.Print wb.Name '>>EmptyTest.xlsx - OK

End Sub

Totally reproducible on my system (win10/Office 365)

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • 1
    Thanks Tim! So to prevent my code from having issues, should I add a part that checks to see if the wb is already open? – z3115 Jun 27 '19 at 14:04
0

I think your problem is that you are opening Workbook B. You state, if you close and re-open it then it works.

Therefore, any changes you have made to Workbook B will be lost as it will re-open the original workbook. (Auto Save does not actually save the workbook, it saves a separate copy.)

You need instead to check if the workbook is already open and only re-open it if it is not currently open.

(Very rough code)

Sub test()
    Dim wb As Workbook
    For Each wb In ThisWorkbook.Application.Workbooks
        If wb.Path & "\" & wb.Name = "U:\workbookB.xlsx" Then Exit For
    Next
    If wb.Path & "\" & wb.Name <> "U:\workbookB.xlsx" Then Set wb = ThisWorkbook.Application.Workbooks.Open("U:\workbookB.xlsx")
    wb.Worksheets("ED").Range("Z1").Value = "TEST"
End Sub

But (as others have said) you should really check that there are no other instances of Excel running and account for them also.