Is there a calculation method that waits for an OLAP refresh?

[seann]

Hi Guys,

I am using Excel 2007 and have a data connection to an OLAP cube. My worksheet has numerous formulas such as cubevalue & cubemember etc. I have finished my report and would like to email it out automatically every morning.

My problem is in the code when I call ActiveWorkbook.RefreshAll to refresh the data this is executed in the background and the code continues to run even though the data hasn't been updated.

I have tried
1.Application.Wait (Now + TimeValue("0:01:00")) although this doesn't work because it also stops the cube query running
2. I have read there is a new calculation method available which may do what I require although the documentation is lacking. The method is CalculateUntilAsyn<wbr>cQueriesDone();

So if anyone knows how to get CalculateUntilAsyn<wbr>cQueriesDone to work or has another way to get this to run in VB your help would be welcome as I am stumped!

Thank you

Seann

P.S Turning of background refresh isn't an option - I am sure there is a way around without resorting to turning background refresh off.

 

[Andrew Poulsom]

Is it?

Application.CalculateUntil<WBR>AsyncQueri<WBR>esDone

 

[seann]

Is it?

Application.CalculateUntil<wbr>AsyncQueri<wbr>esDone

Yes that is correct. I believe that is the answer but the documentation on how to use it is lacking. Any ideas?

 

[Andrew Poulsom]

I'm not sure, but have you tried putting it after your RefreshAll statement?

 

[seann]

Sorry for not reporting back on this one. The above function does work just after the refresh call.

 

[DamageZ]

Thanks guys, this is exactly what I have been looking for.

THANKS HEAPS!!!