Wait for Cube Values to return data before continuing macro

[DamageZ]

Hi All,

I've been trying to find a way to have my macro wait until after my data has refreshed to continue. I'm using Excel 2010.

I have combined our Sales Cube & Stock Cube into a PivotTable and then converted the PT into formulas. I need to do this so that I can minimize the amount of data shown and be very specific with it.

I have a macro that adjusts the details of the values to be returned by this and then adds a vlookup to return data to another sheet. This is where my problems begin;

IDEA 1A : 'ActiveWorkbook.RefreshAll' will refresh the data, however doesn't wait for this action to be finalised before continuing. Which results in a debug on the vlookup line of code as the data is still in "#GETTING_DATA"

IDEA 1B : 'ActiveWorkbook.Connections("Stock Cube").Refresh' same result as above.

IDEA 2 : 'Application.Wait (Now + TimeValue("0:00:10"))' also makes the refresh wait, and even if it didn't I couldn't be sure I was leaving enough time for a slower machine to retrieve data without going overboard and waiting for 90+ seconds

IDEA 3 : 'Sheets("Calc").PivotTables("PivotTable5").PivotCache.Refresh' wont work as it is no longer a pivot table, it's simply an array of CUBEVALUE & CUBEMEMBER formulas.

IDEA 4 : Adding a formula to determine if a CUBEVALUE has returned a number yet doesn't work either. Anytime the cell is showing "#GETTING_DATA", any formula pointing to it returns "#GETTING_DATA". Also, any line of code pointing to that cell returns the Type_Mismatch error.

I'm left with attempting to get my macro to inanely loop for a long period of time. I.E. A1 = A1 + 1, Loop Until 1,000,000. This still has the same problems as IDEA 2 does.

I haven't attached the spreadsheet as no one would have access to the cubes and thus no data would be displayed.

I've read a bunch of other forums and posts and I can't find anything that deals with this. I'm really hoping someone else has had a similar issue and found a resolution.

Thanks for taking the time to read through this!!

 

[DamageZ]

I eventually stumble on to this thread.

http://www.mrexcel.com/forum/excel-questions/404331-there-calculation-method-waits-olap-refresh.html

After RefreshAll;
Application.CalculateUntilAsyncQueriesDone

 

[eric cannelora]

Application.CalculateUntilAsyncQueriesDone

just what I needed. This thread may be a couple years old, but I found it just in time

 

[DamageZ]

I'm glad after 2 years, a question I posted then answered myself has finally helped someone else. Enjoy!

 

[celias]

Yup! And 9 years later, still doing the magic of making the day of someone in the world, @DamageZ! Thank you!

 

[DeepEureka]

I eventually stumble on to this thread.
Is there a calculation method that waits for an OLAP refresh?

After RefreshAll;
Application.CalculateUntilAsyncQueriesDone

Well... It's September 2022 and this post is still very helpful!
I just registered to MrExcel to say thank you.

It really helped a lot with a XLSM full of CUBEVALUE formulas!
David