Macro is too fast for SQL request.
 

I have a macro which automates my whole spread sheet which consists of
Database references and SQL statements. I have included the macro to update
the
SQL statements before I save the sheet using the current date as a filename.
But I find
that because there is a lag for the SQL request the file is saved without
the current SQL
data. But as soon as I (F9) recalculate the sheet after the macro has
completed it shows the current
data!? I am specifying in the macro to CALCULATE before it saves but this
does not help.
Is there any way to suspend the macro as it runs the SQL update all command?
Even if it is a preset time
since the maximum time to wait for the Request would never be greater than
15s.

Thanks again to all those generous people sharing their experience with us
who are in need.

Macro is too fast for SQL request.
 

If you are using querytables, then you need to do foreground queries and the
macro will wait for them to complete

Dim qt as QueryTable
for each qt in ActiveSheet.querytables
qt.Refresh BackgroundQuery:=False
Next

--
Regards,
Tom Ogilvy


Rudolphs wrote in message
...
I have a macro which automates my whole spread sheet which consists of
Database references and SQL statements. I have included the macro to
update
the
SQL statements before I save the sheet using the current date as a
filename.
But I find
that because there is a lag for the SQL request the file is saved without
the current SQL
data. But as soon as I (F9) recalculate the sheet after the macro has
completed it shows the current
data!? I am specifying in the macro to CALCULATE before it saves but this
does not help.
Is there any way to suspend the macro as it runs the SQL update all
command?
Even if it is a preset time
since the maximum time to wait for the Request would never be greater than
15s.

Thanks again to all those generous people sharing their experience with us
who are in need.