Switching between different workbooks without using .activate

Question

I want to reference different workbooks in my code and I have used this code:

Dim Basicwb As Excel.Workbook
Dim Basic As Excel.Application

Set Basic = New Excel.Application
Set Basicwb = Basic.Workbooks.Open("X:\Job\Masroori\3-042-PMS.xlsx")

but the problem is how can I refrence it if I dont want to open it each time. I used this code (without .Open) but I get this Error! : "Subscript out of range"

Set Basicwb = Basic.Workbooks("X:\Job\Masroori\3-042-PMS.xlsx")

Also, I dont want to activate the workbook each time, Is there any way?

score 2 · Accepted Answer · edited May 23 '17 at 11:50

Taken from the msdn site for the Workbooks property:

"Returns a Workbooks collection that represents all the open workbooks. Read-only."

hence the last line of your code gives you an error since the file is not open. AFAIK, you cannot reference objects within a workbook if that workbook is not open. You can access whatever a workbook has without activating it (so without using .Activate), but it has to be open. Maybe this SO question is of help to you:

Open Excel file for reading with VBA without display

If your workbook is open, you can do the following:

Dim wBook as Excel.Workbook, rngTemp as range
Set wBook = workbooks("wbName.xls")
With wBook
    ' Do stuff, no need to activate. Example:
    set rngTemp=.sheets(1).usedRange
End With

I hope this helps?

Switching between different workbooks without using .activate

1 Answers1