I have an activeX object (a chart) embedded in an excel worksheet. This object has a refresh method and two events, chart complete and chartfailed. If the refresh occurs succesfully a chartcomplete event occurs.
I have a macro which calls the refresh method on the activeX object. The problem is that once i have called refresh program execution continues without waiting for the refresh to complete. I want to save the refreshed graph so i need to wait for that event to occur. If excel remains open this happens and everything is fine. However i need to call the macro remotely from a java program. I do this by calling a VB Script file. This opens excel refreshes the chart but then shuts down after calling refresh, ie before the refreshed chart could be saved.
I have tried to call wait in the macro until i toggle a boolean value after the event occurs. However wait puts excel into a state where it can longer recieve the event.
I have also tried a repetitive while statement until the event occurs but the program basically just gets stuck in the loop and all the cpu is used up grinding the computer to a halt.
Do you have any idea how i would begin to synchronize the macro. Is there for example a way to pause the program until an event occurs.
Any help would be greatly appreciated.
