Ok so this may be specific to the reporting tool we are using, but that vendor is not accustomed to advanced excel so has been useless in solving this issue - hoping someone here can give me some tips!

So we have an OLAP reporting tool that links to excel - excel can pull and push data from this OLAP cube.

I need to write a macro to run through a number of workbooks, get them to refresh the data (from the OLAP cube), and then save and close. If not for the OLAP bit I know how to do this.

My hitch is that if I were doing it manually, all I'd need to do to refresh the data is F9. However within a running macro, this doesn't refresh. Calculate function doesn't refresh it, turning off and on again automatic calculation doesn't refresh it. The only thing that does, bizarrely, is OnCalculateHandler. But second time that loops - excel crashes.

Any ideas of something I could try?