Delay Access procedure until Excel finishes calculation

  • Thread starter Thread starter Zill
  • Start date Start date
Z

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
 
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.
 
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.
 
Thanks for the good ideas. I will have to experiment to make it work.
Not too sure about the exact VBA code to be used as still fumbling around
with the syntax :-)
Any examples gratefully appreciated.
Zill
 
Back
Top