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

"Roger" wrote in message ...
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

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

--
Regards,
Tom Ogilvy



"Roger" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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


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

Cheers,

Roger