• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

Wait Till Queries Have Refreshed

I'm trying to execute the code below but I'm getting an error that says the operation cannot be done because the data is refreshing in the background.  How do I get it to wait until the queries have refreshed?  I already have "enable background refresh" selected for all the data connections.
For Each wks In ActiveWorkbook.Worksheets
   For Each ObjList In wks.ListObjects
      ObjList.Unlist
   Next ObjList
 Next wks

Open in new window

0
error_prone
Asked:
error_prone
  • 12
  • 6
  • 3
  • +1
1 Solution
 
TracyVBA DeveloperCommented:
Try this to pause you're code from running, change the milliseconds to whatever works best for your wait time needed:

Declaration
Private Declare Sub Sleep Lib "kernel32" _
 (ByVal dwMilliseconds As Long)


Sub Procedure
Sub Wait(Seconds As Single)

 Dim lMilliSeconds As Long
 lMilliSeconds = Seconds * 1000
 Sleep lMilliSeconds

End Sub
0
 
TracyVBA DeveloperCommented:
Alternatively, you can try this to pause for 5 seconds:

Sub Wait()
   Application.Wait Time + TimeSerial(0, 0, 5)
End Sub
0
 
error_proneAuthor Commented:
Is there anything else besides that or application.wait?  The thing is I won't be able to predict how long some of these queries will run.  I will be re-using this code several times.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
TracyVBA DeveloperCommented:
Instead of hardcoding the wait time, you can pass it in with a variable, and then each time you call it, you can call it with a different amount of seconds.

Sub Wait(waitTime as Integer)
   Application.Wait Time + TimeSerial(0, 0, waitTime)
End Sub
0
 
error_proneAuthor Commented:
That's not really ideal since the whole point of what I'm doing is to automate a process...
0
 
error_proneAuthor Commented:
Can't I do something where I check to see if the queries are currently refreshing before I execute the code?
0
 
error_proneAuthor Commented:
And then loop until the query refresh property is false....???
0
 
LD147Commented:
You could turn off Automatic workbook calculations from the options, and then when you're sure it's not processing anything, press F9 to do a manual refresh.
excel.JPG
0
 
Rory ArchibaldCommented:
Have you tried checking the Listobject's Querytable.Refreshing property? If it's true, then it's still refreshing.
0
 
Rory ArchibaldCommented:
In other words:

For Each wks In ActiveWorkbook.Worksheets
   For Each ObjList In wks.ListObjects
      do while objlist.querytable.refreshing
         doevents
      loop
      ObjList.Unlist
   Next ObjList
 Next wks

Open in new window

0
 
error_proneAuthor Commented:
@ rorya....hmm...the module says it's still running....I've never seen it take this long before...
0
 
error_proneAuthor Commented:
...to refresh the queries, I mean...
0
 
error_proneAuthor Commented:
@rorya...It's looping endlessly I think.  I had to stop the code from running.  This particular workbook only takes less than a minute to refresh all data connections.  
0
 
Rory ArchibaldCommented:
What kind of connections are they, and does the same code also refresh them?
0
 
error_proneAuthor Commented:
They connect to Microsoft Access queries.  Yes...the sub gets called from another sub that does an ActiveWorkbook.RefreshAll
0
 
Rory ArchibaldCommented:
It would be a lot easier then not to use Refreshall but to refresh each table individually in a loop, specifying NOT to use a background query, then Unlist it. That way you know the query will have refreshed before the next line is processed.
0
 
error_proneAuthor Commented:
Ok, but I have no idea how to do that...
0
 
Rory ArchibaldCommented:
Try
For Each wks In ActiveWorkbook.Worksheets 
   For Each ObjList In wks.ListObjects 
      objlist.querytable.refresh False
      ObjList.Unlist 
   Next ObjList 
 Next wks

Open in new window

0
 
error_proneAuthor Commented:
I don't see where in the code it would refresh each connection one by one...
0
 
Rory ArchibaldCommented:
Line 3. :)
0
 
error_proneAuthor Commented:
Same error..."operation cannot be done..."
0
 
error_proneAuthor Commented:
Forgot to take out my original refreshall command
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 12
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now