I have a OLAP Data Model that is published on the Power-BI Services. I built a report using CUBE formulas. I need to do a bit of Hiding and Showing Columns based on the CUBEVALUE results. And I need to print PDF's for a whole bunch of slicer selections. Naturally, I need my VBA Sub to wait until all the CUBE formulas finish refreshing.
I know that the Application.CalculateUntilAsyncQueriesDone method will let me wait until the refresh completes. However, in Excel 2016, it just seems to crash excel. It just waits indefinitely.
Excel Version: Microsoft Excel for Microsoft 365 MSO (16.0.13001.20254) 64-bit
I faced the same issue with Power-Query Queries, but setting the BackgroundQuery property of all the Queries to FALSE before calling Application.CalculateUntilAsyncQueriesDone solved the crashing problem. Unfortunately, the BackgroundQuery property cannot be set to FALSE for an OLAP Data Connection.
The following technique did not work either:
Do Until Application.CalculationState = xlDone
Loop
The refresh starts a bit late, and during that time the Application.CalculationState is xlDone and the code just does not wait for the refresh to even start. If I use the Application.Wait method, the Query also waits to refresh.
Is this just on my computer? Or, are all of you facing issues with Application.CalculateUntilAsyncQueriesDone?
How have you worked around this issue?
I've been having a similar issue this but the commonality among all of my issues is that Application.Calculation was set to xlCalculationManual when CalculateUntilAsyncQueriesDone is triggered. Before that, I tried a series of Calculate, CalculateFull, and CalculateFullRebuild to no avail.
You may need to implement something like the following to get your script to work:
With Application
.Calculation = xlCalculationAutomatic
.CalculateUntilAsyncQueriesDone
Do Until .CalculationState = xlDone
Loop
.Calculation = xlCalculationManual
End With