Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error in Excel 2007 code | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Error Code 1004 when exporting from VB app to Excel 2007 | Excel Discussion (Misc queries) | |||
Why doesn't Excel 2007 record charting and office art macro code? | Excel Discussion (Misc queries) | |||
Can you call a Web Service from Excel 2007 without writing code? | Excel Discussion (Misc queries) |