Wait until MSQuery Refresh finished
 

I have a simple excel file that uses MSQuery to pull data from an Oracle DB.
I have a 1 line macro tied to a button so that the user can open the file
and click to refresh (line is just activeworkbook.refreshall).

I want to add a second line of code to save a copy of the file after the
refresh is completed. Code would be..

ActiveWorkbook.SaveCopyAs ("N:\Common\Resale Pricing Team\BE_CR.xls")

The code works, however it saves before the refresh is completed so the data
is not finished pulling into the sheet. Is there a way to have the macro
wait until it determines that the MSQuery is finished refreshing before it
does the SaveCopyAs code? Would rather not do a hard coded Application.Wait
command for x seconds because I don't know how quickly the query will
finished each time it is refreshed (depends on how much data is coming
back). Any thoughts would be greatly appreciated.

c-

Wait until MSQuery Refresh finished
 

See response to post "Help! Query Refreshing" also from today in this newsgroup
--
- K Dales


"ChrisR" wrote:

I have a simple excel file that uses MSQuery to pull data from an Oracle DB.
I have a 1 line macro tied to a button so that the user can open the file
and click to refresh (line is just activeworkbook.refreshall).

I want to add a second line of code to save a copy of the file after the
refresh is completed. Code would be..

ActiveWorkbook.SaveCopyAs ("N:\Common\Resale Pricing Team\BE_CR.xls")

The code works, however it saves before the refresh is completed so the data
is not finished pulling into the sheet. Is there a way to have the macro
wait until it determines that the MSQuery is finished refreshing before it
does the SaveCopyAs code? Would rather not do a hard coded Application.Wait
command for x seconds because I don't know how quickly the query will
finished each time it is refreshed (depends on how much data is coming
back). Any thoughts would be greatly appreciated.

c-