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

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!

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

Why are you doing the If, does this work?

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


--
__________________________________
HTH

Bob

"Ron West" wrote in message
...
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!

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

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!


"Bob Phillips" wrote:

Why are you doing the If, does this work?

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


--
__________________________________
HTH

Bob

"Ron West" wrote in message
...
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!