Pausing macro until .xla function completes

At work we use the Hyperion Financial Management application (HFM). This has an add on for Excel (.xla) that allows us to extract values from the Hyperion backend data into Excel. We create Excel formulas that describe the data we want to retrieve and then select the "Refresh Retrieve" option from the HFM menu in Excel to retrieve the financial data.

Since I refresh the same retrieves over and over I would like to automate my monthly reporting tasks that basically consist of refreshing retrieves in multiple workbooks, printing the new reports and exiting the workbook.

I have tried for months through our company IT HFM specialists to find out how to call a "refresh" from VBA. They have apparently not received a response from Hyperion and so I can't call the routine from the .xla code. However, after a very long search I found the SendKeys function in VBA and can now at least execute a "refresh" by simulating the keyboard actions to run a refresh manually.

Here is my problem. When I kick off a refresh, the entire process can take up to a minute or two. How can I get my macro to wait until the refresh is finished before continuing? The cursor goes into an hourglass while "refresh" is being carried out. I can get Excel to "wait" for a specified time, but that seems to screw up the "refresh". It seems that any continuation of the macro interrupts the refresh process. Anyway I would like to have Excel wait only while the cursor is in the hourglass form. When the refresh is done, I would like the macro to continue immediately.

Bottom line: Is there a way to have the refresh function maintain control until the refresh has finished? Does it make a difference that I'm "calling" the refresh via the SendKeys command rather than directly from the .xla?

Thanks.

I have extensive experience using VBA to interface with Hyperion Essbase(pulling, sending, calculating, etc)...but none with HFM.

If there is an HFM VBA code module from Hyperion it hopefully includes a synchronous/asynchronous parameter for pulling data. You'd want the pull to be synchronous...so processing waits for the pull to finish.

I hope that helps.
Post back if you have more questions.

I have no expeirence with Hyperion Financial Management

Can you use the Application.Cursor command to test the mouse shape

maybe something like this

Please Login or Register  to view this content.

you can use like

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

in macro