Timing problem

[David Jenkins]

Here's my situation:

1) Workbook1 opens Workbook2 (in Workbook1's Workbook_Open
sub); code looks something like;
Workbooks.Open ("MktStatsData.xls")
DoOtherStuff

2) Workbook2 contains, in its Workbook_Open sub, the line
Application.OnTime Now + TimeSerial(0, 0, 30), _
"DoInitialAlign"
- this is necessary because workbook2 contains links to
a DDE server, and "DoInitialAlign" shouldn't be launched
until all the links are updated
3) however, workbook1 also depends on workbook2 having
finished running "DoInitialAlign", and there's my problem:
since Workbook2 simply opens, sets up the OnTime event,
and is "done" opening, the flow comes back to Workbook1
(at "DoOtherStuff") before Workbook2 is ready for
Workbook1 to access it.
So how can I have Workbook1 wait until Workbook2 has
actually finished running "DoInitialAlign"? Or can
someone think of a better solution?

TIA

David Jenkins

 

[Stephen Bullen]

Hi David,

So how can I have Workbook1 wait until Workbook2 has
actually finished running "DoInitialAlign"? Or can
someone think of a better solution?

Obviously you can't <g>. If Workbook2 knows it's being started by
workbook 1, it could use application.run to call the code in workbook1
from it's DoInitialAlign routine. If not, you need to decide how does
workbook1 know when/if workbook is ready? For example, workbook2 could
set a cell value to True in the DoInitialAlign routine. Workbook1 has
a repeating OnTime procedure that checks the cell value every few
seconds, continuing its stuff when it sees the True in there.

The solution depends on whether you can modify workbook2. If so,
calling back into workbook1 (if open) would be the best solution.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

 

[Tushar Mehta]

Another solution depends on whether DoInitialAlign can be run twice
without harm and whether it is externally accessible.

If so, have workbook1 schedule a procedure within itself for 30 seconds
later and have this procedure call the DoInitialAlign procedure with
the Application.Run method.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions