Access Not Waiting

[vbc22]

I'm looking to add a line in my code that will halt the next code from executing in Access.

Following is my routine for a button that refreshes a web query in an Excel file. Instead of waiting until the line, xl.ActiveCell.QueryTable.Refresh completes, Access immediately goes to the next line executing the 'save as' operation. This in turn, prompts a message from Excel saying:

"This action will cancel a pending refresh data command. continue?"


Here's the full routine code:
***********************************************************
Private Sub btnUpdateExcelFile_Click()
Dim xl As Excel.Application

Set xl = New Excel.Application

xl.Workbooks.Open "C:testFile.xls"

xl.ActiveCell.QueryTable.Refresh <---- PAUSE HERE UNTIL COMPLETE.

xl.ActiveWorkbook.SaveAs FileName:="testfile_newUpdate.xls"

xl.Quit
Set xl = Nothing

End Sub
***********************************************************

Does anyone know what I need to add to halt Access?

Regards.

 

[vbc22]

I found my answer.

I needed to do a looped based on the refreshing property of the query table.

Do
Loop Until xl.ActiveCell.QueryTable.Refreshing = False

 

[PHV]

Simply replace this:
xl.ActiveCell.QueryTable.Refresh
By this:
xl.ActiveCell.QueryTable.Refresh False # NO background query

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, sorry for the typo
Simply replace this:
xl.ActiveCell.QueryTable.Refresh
By this:
xl.ActiveCell.QueryTable.Refresh False [green]' NO background query[/green]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vbc22]

Hi PHV,

I just happened to see this thread again, and noticed that I didn't acknowledge your helpful posts. Sorry mate.

Thanks for the helpful response.

Regards.