Giving time for Excel links to update

[worldcup2010]

Hi,

I have a word document with an excel graph link. When I ask to refresh the data in word the excel data doesn't refresh properly. I think this must be because excel graph doesn't have time to refresh.

When I open the excel file separately it take a couple seconds for the graph to update. The data it is linked to is in the same file and consists of a few bloomberg cell equations that take a little time to update.

I've been trying various combinations of Application.Wait and Application.Ontime at different stages (eg at Workbook_SheetCalculate, beforeclose,...) without success

Any suggestions/solutions?

Thanks,

Pat

 

[worldcup2010]

I found a solution to this problem. I have a macro that opens all the excel files one by one and then closes all of them after a delay. This allows the workbooks enough time to retrieve the data from bloomberg:

Application.OnTime Now + TimeValue("00:00:07"), "CloseAndSaveOpenWorkbooks"

also before closing the excel files I set the calculation method to manual:

Wkb.Application.Calculation = xlCalculationManual

so that any link to the file will not recalculate anything, only the macro does data retrieval and calculation when it is run.