MDX query detecting when finished

[PJBM]

Hi All

I have a workbook with numerouse MDX queries. To refresh everything I can just hit the refreshall button and wait ... to code this I can code the refreshall. What I want to do is automate this and then close the workbook. The problem is detecting when the sheet(s) have finished the updates. Has anyone any ideas how to figure out when the mdx has completed the updates.

Note the code line of refreshall runs through but the application continues to update all the cells with MDX in them

Thanks

Phil

 

[Webster]

I don't have the definitive answer, but maybe I can give some direction. The problem is that there are multiple activities kicked off and they continue to run after the code completes. You could track the opening and closing of the connection at the workbook level or the worksheet events "Calculate" and "Pivot Table Update". A very simplistic approach would be to query the pivot table "Last Refresh" date and end the code when they are all updated with the current date and time.

 

[Fazza]

Hi, Phil.

I am unfamiliar with MDX queries though am familiar with basic queries - query tables. I don't know if the info following is relevant to MDX queries.

Can/have you set the queries to refresh in the background?

For the queries I use, I almost never use refreshall. Preferring instead to loop through and refresh each query.

If you have/can set background refreshing the refreshall will account for that. If not, you can explicitly set it by looping. So, something like, untested, for pre Excel 2007

dim wks as worksheet
dim qt as querytable
 
for each wks in activeworkbook.worksheets
for each qt in wks.querytables
qt.refresh backgroundquery:=false
next qt
next wks
 
set qt=nothing
set wks=nothing

HTH, Fazza