Delay Access procedure until Excel finishes calculation

[Zill]

I have an Access 97 DB which needs to use Excel 97 to perform complex
calculations before returning the results to an Access table. The whole
process must be automated.
I use a Access VBA sub procedure to open the Excel file, triggering
"Workbook Open" code which then retrieves latest data from the Access DB via
MS Query. The Excel file then calculates the new data, saves and closes the
file.
I have another Access sub procedure to "TransferSpreadsheet", retrieving the
new data from Excel and saving it to the table.
Problem is that if the two Access sub procedures are chained, then the
Access table is updated _before_ Excel has finished its data updating and
calculation.
I do not want to put a fixed time delay between the two procedures, as the
delay varies with amount of data and network speeds etc.
Any ideas how I can delay the second Access sub until Excel has finished
recalculating?
TIA.
Zill

 

[Bas Cost Budde]

You could do the good old semaphore trick:

upon execution start, Excel creates a file. Access knows this file by
name and waits while it exists.
When Excel has finished, it removes the file. Access no longer sees it,
and resumes execution.

 

[onedaywhen]

How about this: your Excel macro sets a flag in the DB when it has
finshed calculating e.g. in the Workbook_BeforeClose event. Your
second MS Access sub procedure only runs the 'transfer spreadsheet'
code when it has detected that the flag has been updated by Excel,
perhaps with a slight delay to give the workbook time to close.