Reference to other workbook problem

  • Thread starter Thread starter Terry Carpenter
  • Start date Start date
T

Terry Carpenter

Hello!,
I have two workbooks, wb1 and wb2, with macro code in them. wb2
has a hard reference to wb1 by selecting and setting a reference to
wb1 from the Tools menu in the IDE. This way, wb2 can access objects
such as a user-defined Public array of structs with code like this:

Project.UserDefinedType

Rather than hard coding the reference to wb1, I am trying to set it in
wb2 something like this:

Set WBref = Workbooks(wb1.Name)

This works but when I get to the code where it's supposed to access
the user-defined type, it doesn't see it. The code for this looks
like:

For i to Ubound(WBref.udt) to 0 Step -1

Where udt is supposed to be the user-defined Public array of structs.
The problem is WBref doesn't have any of the user-defined objects
available in the pull-down. Am I stuck with a hard reference to wb1?

Thanks!
Terry
 
Terry

How is WBref dimmed? It appears that all your doing is creating another
Workbook variable that will be exactly the same as wb1. To add a reference
to another workbook, use code like this

Sub test()

Dim wb As Workbook

Set wb = Workbooks("book3.xls")

ThisWorkbook.VBProject.References.AddFromfile wb.FullName

End Sub

You can't use anything from book3.xls in the same module where this sub is
or you'll get a compile error - it won't know what those things are because
the reference hasn't been set. So this code will have to go in its own
module.
 
Back
Top