Excel Webservice Refresh (1 Viewer)

Hi,

I have what (I think) should be a relatively simple problem to solve, but I'm sure there must be a better way than my current solution!

I am using the Webservice function to obtain stock price data from Yahoo Finance, that all works correctly, however I can not work out any way to refresh the data without clicking into the cell and pressing enter.

The way I've automated this is to simply create a button that loops through all the cells, then sends an F2 keystroke, followed by an Enter keystroke.

This works, but obviously is slow and seems to be an untidy way of doing things.

Can anyone think of a better way to refresh (the usual refresh options don't seem to work, I presume as I'm obtaining data from an external site)?

For reference a typical cell is as follows:

=WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s="&$A2&"&f=n")

(where A2 is the stock ticker, this particular cell would bring up the stock descrption. If anyone wants a sample ticker to play with try ALP1.L).

There are several ways to accomplish this.
Be careful! using timers that spawn an object without closing the last object can be ... really bad.
When TESTING - be sure to open Task Manager. If new instance of Excel are spawned every cycle, stop the session!

The key words are below in some code. There are many examples to find on the web. There are many uses, flavors and requirements.
For example: run something once each month (like a monthly report)
Run something at an exact time (like someone's birth day!)
(will the place I worked at last enjoy that one or what! LOL)

Take a look, at the VBA Application.OnTime
Be sure to post your code so others can enjoy

Public variables in a standard code module, outside of and before any procedure

Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "TheSub" ' the name of the procedure to run

To start a repeatable timer, create a procedure named StartTimer as shown below:
Sub StartTimer() RunWhen = Now + TimeSerial(0,0,cRunIntervalSeconds) Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _ Schedule:=True End Sub Sub TheSub() '''''''''''''''''''''''' ' Your code here '''''''''''''''''''''''' StartTimer ' Reschedule the procedure End Sub Sub StopTimer() On Error Resume Next Application.OnTime EarliestTime:=RunWhen,Procedure:=cRunWhat, _ Schedule:=False End Sub -------------------------------------------------------------Windows API method Excel 2000 or laterTo use Windows timers, paste the following code into a standard code module:
Public Declare Function SetTimer Lib "user32" ( _ ByVal HWnd As Long, _ ByVal nIDEvent As Long, _ ByVal uElapse As Long, _ ByVal lpTimerFunc As Long) As Long Public Declare Function KillTimer Lib "user32" ( _ ByVal HWnd As Long, _ ByVal nIDEvent As Long) As Long Public TimerID As Long Public TimerSeconds As Single Sub StartTimer() TimerSeconds = 1 ' how often to "pop" the timer. TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc) End Sub Sub EndTimer() On Error Resume Next KillTimer 0&, TimerID End Sub Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _ ByVal nIDEvent As Long, ByVal dwTimer As Long) '''''' ' This procedure is called by Windows. Put your ' code here. '''''' End Sub

Sorry it took so long to get back. After spending four hours to solve a simple problem, it turned out that it might help your question.

Problem: Trying to insert a dataconnection in Excel to a make a worksheet linked. Using the Get Data using existing connections is well known.
To do this with vba program, the recorder will not play back.
The code below is for creating an Excel Application and assigning it to a object variable ObjXL. The code to add the data source is shown below.

The problem was that the code would execute, then bring up a WINDOWS Error message. Choose OK and the code would continue and work.
But, a system error in the middle of processing is hurtful to a programmer.

It is my belief that all of the older codes that were claimed to work were part of a MS update. After hours of searching, the FINDER was in a recent article. Simply adding that made the code work perfectly.

B.T.W. the ODC was created in Excel manually - then use the export as ODC. This will be shipped with the project. The User name and path will become a standard variable instead of a string. I sure miss Citrix. It took care of all those pesky string requirements!

The FINDER; key word was identified in a method to join a Web site e.g. Wall Street Journal. perhaps FINDER is new for XML and InterOp?