Assigning Open Workbook to variable

[spydey]

If I already have a workbook open, and know the name of the workbook, why can't I simply assign the workbook to a variable?

Example:

If I have workbook A and workbook B, and both are open.
The code is in workbook A.

Why can't I do something simple like:

Sub Assign_WB

Dim wb as Workbook

Set wb = Workbooks("Workbook B")

For Each ws In wb.Worksheets

etc etc etc

Next

End Sub

It seems like all the ways I have been doing it (as per research and internet) involve more code than what I would assume is needed to simply state:

Variable X is "Workbook B"

Granted the code I have been using works, but I just don't fully understand why ... and why the above snippet wouldn't work ....

I am looking for more of the concept/principle than fixing the above code .... but writing the above code correctly would help with understanding the concept/principle.

-Spydey

 

[JoeMo]

If a workbook is open, you need only its name for identification, but that name must include the file extension. So,

Dim wb as workbook
Set wb = Workbooks("Workbook B.xlsm")

is ok, but
Set wb = Workbooks("Workbook B")
will not fly.

 

[Fluff]

You can actually use

Set wb = Workbooks("Workbook B")

if your folder settings are set to "Hide known file extensions".

That said I would recommend always including the extension

 

[spydey]

@ JoeMo & Fluff

Thanks for the insight, I appreciate it.

So if I include the file extension type in the code, then it should work fine, correct?

Hmmm, ok.

But it must be the file name, not the whole path, right?

So

Dim wb as Workbook

Set wb = Workbooks("WorkBook B.xlsm")

would work, as you both pointed out ....

but what about:

Dim wb as Workbook

Set wb = Workbooks("C:\Location\Of\File\Workbook B.xlsm"

Granted, workbook B is already open.

I tend to get errors with that ..... and I am trying to figure out the different requirements for the use of the workbooks object (it is an object, right???)


-Spydey

P.S. This is just me fiddling around, trying to better understand VBA, objects, items, etc etc., and their correct uses.

 

[JoeMo]

If the workbook is already open, using the full name (path + name) will trigger an error.

Here's a simple UDF I use to avoid errors altogether. If the function returns False then you can open the workbook using the full name.

Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
   
WorkBookNotOpen:
End Function