Convert String to Workbook object

[dhancy]

Hello,

I have this code:

Dim myWB as string 
Dim ws as worksheet

myWB = "myWorkBook.xlsx"

I now want to refer to myWB as a Workbook object.

In other words, I'd like do something like this:

for each ws in my WB
...
next s

Any ideas how to accomplish this? Thanks!


Dennis

 

[Fluff]

Like

Dim myWB As Workbook
Dim ws As Worksheet

Set myWB = Workbooks("myWorkBook.xlsx")

 

[dhancy]

Like

Dim myWB As Workbook
Dim ws As Worksheet

Set myWB = Workbooks("myWorkBook.xlsx")

Thanks. Funny thing is, I tried that, but it gave me an error: "Run-time error '9': Subscript out of range".

Is there something else I am missing?

 

[Fluff]

Either the workbook isn't open, or the name (including extension) is wrong.

 

[Leith Ross]

Hello dhancy,

The error indicates that the workbook is not open in your current Excel application.

Dim MyWB As Worksbook
Dim ws As Worksheet

    On Error Resume Next
        Set MyWB = Workbooks("myWorkBook.xlsx")
        If Err = 9 Then Set MyWB = Workbooks.Open("myWorkBook.xlsx")   ' You may need to include the full path to open the workbook.
    On Error GoTo 0

 

[Joe4]

To expand upon what Leith said, the workbook not only has to be open, but it has to be open in the same Excel session.
Depending on how you are opening the file, you can have different Excel sessions open on your computer, and they won't be able to see one another.
If you open the files separately from Windows Explorer, this will typically be the case.

 

[dhancy]

To expand upon what Leith said, the workbook not only has to be open, but it has to be open in the same Excel session.
Depending on how you are opening the file, you can have different Excel sessions open on your computer, and they won't be able to see one another.
If you open the files separately from Windows Explorer, this will typically be the case.

Thanks Joe4, Leith, and Fluff!

It's working now. I appreciate your help on this

dennis

 

[Fluff]

Glad we could help & thanks for the feedback