2

I have a data table that I update with the following code

ActiveWorkbook.Connections("Query - My_Table").Refresh

This data feed the data of some pivot tables.

And I have two buttons. One to update the data table and one to update the pivot tables. I was wondering, if there any way to know when a data table has finished refreshing so I know then I can press the button to refresh the pivot tables?

Update: some people have been asking why I don't update the data table and the pivot table in the same piece of code.

The issue with that is if for example I do the following

ActiveWorkbook.Connections("Query - My_Table").Refresh
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

then the code will start updating the data table and then update the pivot table. so it doesn't wait until my data table has finished refreshing.

Sam
  • 627
  • 2
  • 13
  • Why not combine updating the pivot tables with the data table? If you are going to do it every time, no need to wait. – Darrell H Apr 28 '21 at 12:03
  • Because the data table takes several minutes to update and if I do it all in a single piece of code then it will start updating the data table, then update the pivot table (before the data table finished updating) and it doesn't work for me that way. – Sam Apr 28 '21 at 12:06
  • Try using `Do Events` , then it will execute after completing the previous step – Darrell H Apr 28 '21 at 12:37
  • Does this answer your question? [How to check whether Connection Refresh was successful](https://stackoverflow.com/questions/43548543/how-to-check-whether-connection-refresh-was-successful) – Toni Apr 28 '21 at 12:45

3 Answers3

1

I used

Application.CommandBars("Queries and Connections").Visible=True

This will show me the "Queries & Connections" menu and there I can check if the table has been updated or not.

Many people will use this workbook and I just wanted others to be able to see if the table has updated or not before trying to update the pivot tables.

Sam
  • 627
  • 2
  • 13
0

Try:

RefreshPivotTableButton.Enabled = False
ActiveWorkbook.Connections("Query - My_Table").Refresh
RefreshPivotTableButton.Enabled = True

If it does not work, try this:

How to check whether Connection Refresh was successful

Toni
  • 1,555
  • 4
  • 15
  • 23
  • I don't think that this will work because the table takes several minutes to refresh and once it starts refreshing VBA goes into the next line of code, it wouldn't wait until it finishes refreshing to go into the third line of code that you are showing there. – Sam Apr 28 '21 at 12:03
0

I had similar problem. I solved it by disabling the background refresh in the connection properties of the query. This forces the query to finish before the next step of the code is performed. I suppose you could put this also on the code itself (disabling first, enabling after)

GuestTrial
  • 1