I have written an excel spreadsheet that uses many query's to get info from
an Access db. In some cases, a query uses the results from another query as
parameters. I have set up a macro that refreshes each table separately using
a For...Next loop to refresh each Querytable within each sheet. My thought
was that by running the queries in order, the results would be back on the
first queries before the second queries run. In each case, the spreadsheets
do not update until all the query defs run. I have tried putting a wait
command in and turning on and off screen updating. I am hoping to avoid
using the "Update query when Cell changes" option. Any thoughts?

for each qt in Activesheet.QueryTables
qt.Refresh BackgroundQuery:=False
Next

--
Regards,
Tom Ogilvy


"billybob" wrote:

I have written an excel spreadsheet that uses many query's to get info from
an Access db. In some cases, a query uses the results from another query as
parameters. I have set up a macro that refreshes each table separately using
a For...Next loop to refresh each Querytable within each sheet. My thought
was that by running the queries in order, the results would be back on the
first queries before the second queries run. In each case, the spreadsheets
do not update until all the query defs run. I have tried putting a wait
command in and turning on and off screen updating. I am hoping to avoid
using the "Update query when Cell changes" option. Any thoughts?

This is what I currently have in my code. I run through this for each sheet
in my workbook. The issues is that it does not seem to update the
spreadsheet until after all the pages have been updated. Because subsequent
queries use the previous queries data as parameters, they are not being
updated properly.

Example:
Sheet1 has query a which loops throught the querytable.refresh first
Sheet 2 has query b which uses the results in Sheet1 as parameters. The
for...next loop for sheet2 is after the one for sheet 1, but the parameters
are not being updated until after the macro is complete.

Thanks,
JS

"Tom Ogilvy" wrote:

for each qt in Activesheet.QueryTables
qt.Refresh BackgroundQuery:=False
Next

--
Regards,
Tom Ogilvy


"billybob" wrote:

I have written an excel spreadsheet that uses many query's to get info from
an Access db. In some cases, a query uses the results from another query as
parameters. I have set up a macro that refreshes each table separately using
a For...Next loop to refresh each Querytable within each sheet. My thought
was that by running the queries in order, the results would be back on the
first queries before the second queries run. In each case, the spreadsheets
do not update until all the query defs run. I have tried putting a wait
command in and turning on and off screen updating. I am hoping to avoid
using the "Update query when Cell changes" option. Any thoughts?

So you are already using the backgroundquery:=False parameter?


If so, then you can try adding a doevents

For each sh in Worksheets
for each qt in sh.QueryTables
qt.Refresh BackgroundQuery:=False
doevents
Next
Next

--
Regards,
Tom Ogilvy


"billybob" wrote:

This is what I currently have in my code. I run through this for each sheet
in my workbook. The issues is that it does not seem to update the
spreadsheet until after all the pages have been updated. Because subsequent
queries use the previous queries data as parameters, they are not being
updated properly.

Example:
Sheet1 has query a which loops throught the querytable.refresh first
Sheet 2 has query b which uses the results in Sheet1 as parameters. The
for...next loop for sheet2 is after the one for sheet 1, but the parameters
are not being updated until after the macro is complete.

Thanks,
JS

"Tom Ogilvy" wrote:

for each qt in Activesheet.QueryTables
qt.Refresh BackgroundQuery:=False
Next

--
Regards,
Tom Ogilvy


"billybob" wrote:

I have written an excel spreadsheet that uses many query's to get info from
an Access db. In some cases, a query uses the results from another query as
parameters. I have set up a macro that refreshes each table separately using
a For...Next loop to refresh each Querytable within each sheet. My thought
was that by running the queries in order, the results would be back on the
first queries before the second queries run. In each case, the spreadsheets
do not update until all the query defs run. I have tried putting a wait
command in and turning on and off screen updating. I am hoping to avoid
using the "Update query when Cell changes" option. Any thoughts?