How to set another workbook as a variable

[chrissnead]

I am trying to set an open workbook as variable book2 but keep getting a 'Subscript out of range' error. Below is the code I am working with. It errors at 'Set book2'. It's completely random and will work sometimes but most of the time it doesn't. Could someone help?

Set book1 = ActiveWorkbook
Set book2 = Workbooks("MySpreadsheet.xlsx") ' errors out here


Set lookFor = book1.Sheets(1).Cells(NextRow, 2) ' value to find
Set srchRange = book2.Sheets(1).Range("A:N") 'source


lookFor.Offset(0, 6).Value = Application.VLookup(lookFor, srchRange, 11, False)
lookFor.Offset(0, 7).Value = Application.VLookup(lookFor, srchRange, 10, False)

 

[AlphaFrog]

Is MySpreadsheet.xlsx opened in the same instance of Excel that contains the code?

 

[Joe4]

One way is to first select that other workbook, then just use:
Set book2 = ActiveWorkbook

 

[chrissnead]

No the code I'm creating is actually an add-in. The active workbook is not MySpreadsheet.xlsx

 

[chrissnead]

I already tried doing this and I'm getting the same error.

 

[Joe4]

I already tried doing this and I'm getting the same error.

On which line, exactly?
The selecting of the other workbook, or the setting of the workbook variable.
Note that both workbooks must be in the same Excel session. Depending on how you are opening them, they could be in different Excel sessions.

 

[chrissnead]

Right after setting book1 = ActiveWorkbook I tried activating the other workbook then doing a Set book2 = ActiveWorkbook.

What do you mean by in the same Excel session? They are both "Read Only" aka they aren't saved to the desktop. Does that have anything to do with it?

 

[Joe4]

What is your exact error message?
If you hit debug, which line of code does it highlight in yellow?
That is the line we want to focus on.

If it is highlighting the Activate line for the other workbook, it means you cannot activate it, which means that most likely:
- You are activating it incorrectly
- You have a typo in the file name
- It is in a different Excel session

How exactly are you opening these Excel files?
One way to tell if they are both in the same session is to look at the Project Explorer in the VB Editor. It will list all your open Workbooks in that session. Do you see both of them listed there?

 

[chrissnead]

Highlights Set book2 = Workbooks("MySpreadsheet.xlsx")

I'm opening them from an Outlook email. I am seeing both workbooks in the same session as well.

 

[Joe4]

Set book2 = Workbooks("MySpreadsheet.xlsx")

I thought you said you were activating this other workbook and using:

Set book2 = ActiveWorkbook

like I said up in post 3.

Please post your current code in its entirety, so we can see exactly what you are doing.