Wait for Cube Values to return data before continuing macro

DamageZ

New Member
Joined
Aug 20, 2010
Messages
25
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

New Member
Joined
Aug 20, 2010
Messages
25
I'm glad after 2 years, a question I posted then answered myself has finally helped someone else. Enjoy!
 

celias

New Member
Joined
Oct 1, 2015
Messages
34
Yup! And 9 years later, still doing the magic of making the day of someone in the world, @DamageZ! :) Thank you!
 

Forum statistics

Threads
1,085,253
Messages
5,382,591
Members
401,796
Latest member
Ginger12

Some videos you may like

This Week's Hot Topics

Top