Sequential Sub Procedures

  • Thread starter Thread starter Roger
  • Start date Start date
R

Roger

I have a Sub procedure which pulls data into multiple Excel 97 worksheets
from an Access database via MS Query. The data is refreshed with the
command "ActiveWorkbook.RefreshAll".

I then have another Sub procedure to change row height on all the worksheets
to fit the newly imported data.

My problem is that the row height Sub runs before the RefreshAll command has
finished and so none of the rows are changed to match the new data.

Could anyone suggest how I can make the row height procedure run only when
the RefreshAll command has completed.

TIA

Roger
 
Hi
Try putting
DoEvents
after the refresh.

Alternatively, is your Row Height sub activated by an Event procedure
(like sheet change or something)? If it is, the Refresh might be
triggering it. To stop that, put Application.EnableEvents = False at
the top and Application.EnableEvents = True at the bottom of your
refresh code.

regards
Paul
 
Each query definition should have a backgroundquery parameter. This is
obviously set to True. You need to set it to false and your macro will wait
for the query to complete.

You could change it with this macro

for each sh in ActiveWorkbook.Worksheets
for each qt in sh.QueryTables
qt.BackgroundQuery = False
next
Next
 
Tom Ogilvy said:
Each query definition should have a backgroundquery parameter. This is
obviously set to True. You need to set it to false and your macro will wait
for the query to complete.

You could change it with this macro

for each sh in ActiveWorkbook.Worksheets
for each qt in sh.QueryTables
qt.BackgroundQuery = False
next
Next
Click to expand...

Many thanks for the info Gents - got it running perfectly now.

Cheers,

Roger
 
Back
Top