How to call a macro just after all queries have been refreshed?
in BI 7.0 workbook we have three different queries. We have also a macro which is called after refresh with the correct signiture. What we need is that the macro is called only once after all queries are refreshed. Currently it is called after refresh of every query which means it is executed three times and it takes a long time for the user to wait.
Any help in this issue will be highly appreciated.
Frédéric GAUTHIER replied
What you can do is to check in the macro called everytime which query is refreshed, and to trigger the code only when the last query is refreshed.
By putting a counter or checking the query ID or the resultarea location, you could find the solution.
What I would do is : define an excel cell which will contain the counter.
In the macro, put a code like this one (not real VBa, pseudo code)
If counter >= 3
this ensure resetting the counter at every run
counter = 1
counter = counter + 1
If counter = 3
Your code here