Excel 2007 SP1 - How to do F9 in VBA code?

[Ron West]

I have a spreadsheet that is coded to get Reuters data using the RtGet
function in a few hundred cells, for example this (where B$7 is the ISIN
required):-

=RtGet("IDN_SELECTFEED", B$7, C$7)

Because Reuters is rather leisurely about returning values and reports that
the retrieval is complete when it isn't, we have to sit at the PC hitting F9
every few seconds until nothing changes for a while. This normally happens
after about 15-20 seconds.

I have tried to replace this manual F9-pressing procedure with a Do...Loop

For i = 1 To 6
If Application.Wait(Now + TimeValue("0:00:03")) Then
Calculate
DoEvents
End If
Next

but the "Calculate" keyword appears to do nothing, and the Reuters cells
retain their previous information - but if I press F9 they update.

So I tried replacing the "Calculate" with things like

SendKeys "{F9}", True

or

ActiveWorkbook.RefreshAll

but neither of these do anything to the Reuters data cells either.

How do I get this cell-data-refresh to work in VBA?

Thanks!

 

[Bob Phillips]

Why are you doing the If, does this work?

For i = 1 To 6
Application.Wait(Now + TimeValue("0:00:03"))
Calculate
DoEvents
Next

 

[Ron West]

Sorry - I tried that but it made no difference. I proved that the "Calculate"
was being executed by surrounding it with Debug.Print statements.

If we can't find a way round this, I think this could be a major bug in
Excel 2007 SP1, if VBA "Calculate" can't be relied on to work.

We have all sorts of other financial spreadsheets relevant to the London
Stock Exchange, migrated from XL2000 and XL2003 using Reuters and Bloomberg,
that use "Calculate" - do we have to do through all these and retest them???

ARE YOU THERE, MICROSOFT...??? PLEASE VERIFY that "Calculate" works in the
imminent SP2 release!

 

[Ed Hansberry, MS-MVP/Mobile Devices]

Sorry - I tried that but it made no difference. I proved that the "Calculate"
was being executed by surrounding it with Debug.Print statements.

If we can't find a way round this, I think this could be a major bug in
Excel 2007 SP1, if VBA "Calculate" can't be relied on to work.

We have all sorts of other financial spreadsheets relevant to the London
Stock Exchange, migrated from XL2000 and XL2003 using Reuters and Bloomberg,
that use "Calculate" - do we have to do through all these and retest them???

ARE YOU THERE, MICROSOFT...??? PLEASE VERIFY that "Calculate" works in the
imminent SP2 release!

Just curious - have you tried variants on .calculate, like
Application.CalculateFull or
Application.CalculateFullRebuild

--
__________________________________________________________________________________
Ed Hansberry (Please do *NOT* email me. Post here for the benefit of all)
What is on my Pocket PC? http://www.ehansberry.com/
Microsoft MVP - Mobile Devices www.pocketpc.com
What is an MVP? -