CODEWITHSUNDEEP
excelvba
Sam
Sam

Reputation: 746

How to know when a table has finished updating

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.

Upvotes: 2

Views: 1108

Answers (3)

GuestTrial
GuestTrial

Reputation: 1

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)

Upvotes: 0

Toni
Toni

Reputation: 1585

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

Upvotes: 0

Sam
Sam

Reputation: 746

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.

Upvotes: 1

Related Questions