Reference to a workbook

[tiredofit]

I am trying to refer to a workbook saved on my PC but I don't know why the following code doesn't work:

    Dim wb As Workbook
    Set wb = Workbooks("C:\Users\myname\Documents\myexcelfile.xlsm")

The error message is:

Run-time error '9'

Subscript out of range

When I recorded a macro to open the workbook, this code was generated:

Workbooks.Open Filename:="C:\Users\myname\Documents\myexcelfile.xlsm"

so I know my file and its location is correct.

 

[Fluff]

Is the workbook open?

 

[tiredofit]

Is the workbook open?

No, it's closed.

 

[Fluff]

In that case you need to use

Set wb = Workbooks.Open("C:\Users\myname\Documents\myexcelfile.xlsm")

 

[tiredofit]

In that case you need to use

Set wb = Workbooks.Open("C:\Users\myname\Documents\myexcelfile.xlsm")

Thanks, your suggestion did work but (obviously) opens the workbook.

I recall sometime ago that I could reference a workbook without opening it or am I mistaken?

I tried experimenting by opening myexcelfile.xlsm manually, then running this code from another workbook:

    Dim wb As Workbook
    Set wb = Workbooks("C:\Users\myname\Documents\myexcelfile.xlsm")

but it still failed.

However, this worked:

    Dim wb As Workbook
    Set wb = Workbooks("myexcelfile.xlsm")

 

[Fluff]

You cannot set a variable to a workbook if it is not open.

 

[tiredofit]

You cannot set a variable to a workbook if it is not open.

Thanks for the explanation.

 

[Fluff]

You're welcome.