0

I have a problem with a VBA in my Excel file.

The Excel file uses a data model as well as several pivot tables. The VBA is used to refresh all data connections when the file is opened for the first time in the morning.

The code I used for the refresh is as follows:

ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
If Not Application.CalculationState = xlDone Then
    DoEvents
End If

I then stumbled across the issue, that with this code, my pivot tables (which are not relying on the data model but on different connections to different sql servers) do not refresh. I have disabled background refresh in all pivot tables. Still no difference. After some research I simply added additional code to refresh the missing connections one by one:

ActiveWorkbook.Connections("Connection Name").Refresh

Now comes the tricky part. When opening the file in the morning VBA still only refreshes the data model and does not touch the pivot connections. When I run the code in debugging mode it seems to execute the pivot refresh code but in fact does not do anything. When i refresh all manually with the refresh all button on the data tab and then run the code in debugging mode it works. I also added the code to a button to check if it works and works fine, also works fine without refreshing manually first. The problem remaining is the refresh when opening.

Looking forward to help! I have no clue what to do next. Thanks!

Improve this question
2
  • " I have disabled background refresh in all pivot tables." - have you done the same on all the sql server connections? Control could be returning to the VBA before the queries are complete, so the Pivot table refresh will still be looking at "old" data Commented Sep 10, 2024 at 10:31
  • You could let VBA handle all refreshes. Turn off the async nature of each connection in the code (some connection types offer an argument to do that in the refresh method, others have a separate property to set that). Then run the refreshes in the order you want them to, then refresh all pivot tables. Commented Sep 10, 2024 at 12:20
Related questions
2191
How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?
597
'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine
766
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
Load 6 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.