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.
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.