0

I have a VBA macro (in Excel) that needs to refresh multiples QueryTable. It take a while but I know with the argument "BackgroundQuery" it is possible to make the refresh asynchronous. The issue is that I do not find a way to wait for all of them to finish before continuing my code.

Here is an extract of my current code:

' Refreshing CSV data (Export Availability) (BackgroundQuery:=False make the output sync)
With shExpAvlb.[export_availability].ListObject.QueryTable
    .CommandText = "SELECT * FROM [export_availability] where Country='" & str_Country & "'"
    .Refresh BackgroundQuery:=True
End With

' Refreshing CSV data (production) (BackgroundQuery:=False make the output sync)
With shProd.[production].ListObject.QueryTable
    .CommandText = "SELECT * FROM [production] where Country='" & str_Country & "'"
    .Refresh BackgroundQuery:=True
End With
' ? How to wait for them before continuing my code ?

Thanks a lot for your help !

Max

Max
  • 73
  • 7
  • 1
    https://stackoverflow.com/a/26780134/11683? – GSerg Aug 23 '21 at 09:47
  • Are you doing something useful while waiting? If both queries are to the same server, the difference between executing in parallel and sequentially is negligeable. If (as your code comments suggest), you are reading files, I would expect no gain. The bottleneck is often the data transfer and formatting, so it might be worth specifying the column list to restrict the data volume. Application.ScreenUpdating = False is essential in this situation – grahamj42 Aug 23 '21 at 09:49
  • @GSerg Thanks for this. But it doesn't help much, maybe due to the second comment ! – Max Aug 23 '21 at 10:18
  • @grahamj42 You might be right. All my data are stored in .txt file on a Sharepoint. The txt file are not that big and I pick only required data with SQL command. Also, i've already pass all application settings like, screenupdating, calculation=xlManual, etc. – Max Aug 23 '21 at 10:18

0 Answers0