How to start template with connected workbook

[hstijnen]

Hi,
I've developed a workbook wb1 with a connected workbook wb2. Wb2 is open
with VBA in the Workbook_Open():

Workbooks.Open(ThisWorkbook.Path & "\wb2.xls", 0, True)

Now I've changed wb1 into a template. When I now make a new workbook based
on the template the Workbook_Open() macro is executed. But now
ThisWorkbook.Path is empty! And the connected wb2 cannot be found.

What can I do to open wb2 in this situation?

Thanks for help,

Henk

 

[Tom Ogilvy]

As you have observed, a workbook created from a template has no path until
the workbook is saved.

You could have the open event check for this condition and when found
instruct the user to save the workbook (perhaps close and reopen or use the
BeforeSave event to trigger a delayed macro [using application.ontime] to
open the wb2).

Of course if the open event found the workbook had a path, it could go ahead
and open wb2

Another approach would be to set a reference to Wb2 from the VBE for wb1.

If WB1 will be distributed to different locations, then this would be harder
to manage. If you were only making it a template so the contents would not
be altered and it would always be in one location, this would be OK, but
then you could just hardcode this location in the workbook open.

 

[hstijnen]

Thanks, Tom, for your suggestions. Indeed the template will be stored in
different maps, for it is to be distributed via internet. When the user saves
his workbook, it will get a path, but then also the path to the template map
is not known.

In the meantime I've surrouded the problem by copying WB2 into WB1.

As you have observed, a workbook created from a template has no path until
the workbook is saved.

You could have the open event check for this condition and when found
instruct the user to save the workbook (perhaps close and reopen or use the
BeforeSave event to trigger a delayed macro [using application.ontime] to
open the wb2).

Of course if the open event found the workbook had a path, it could go ahead
and open wb2

Another approach would be to set a reference to Wb2 from the VBE for wb1.

If WB1 will be distributed to different locations, then this would be harder
to manage. If you were only making it a template so the contents would not
be altered and it would always be in one location, this would be OK, but
then you could just hardcode this location in the workbook open.

--
Regards,
Tom Ogilvy

Hi,
I've developed a workbook wb1 with a connected workbook wb2. Wb2 is open
with VBA in the Workbook_Open():

Workbooks.Open(ThisWorkbook.Path & "\wb2.xls", 0, True)

Now I've changed wb1 into a template. When I now make a new workbook based
on the template the Workbook_Open() macro is executed. But now
ThisWorkbook.Path is empty! And the connected wb2 cannot be found.

What can I do to open wb2 in this situation?

Thanks for help,

Henk