Automatically refresh spreadsheet every 5 minutes

[guntoter]

I am building a Excel sheet that polls data from a remote server. We have a PI Server in our plant, and we have an add-on to our Excel called "PI Datalink" which does the work of talking to the Server.
The problem I have is that after polling the remote server, it will keep that data, and since the data in the server is dynamic and constantly changing, I need the Excel sheet to automatically re-poll the data every few minutes.

I can refresh the polling using VB, but I know that it takes an event to do that. We want it to work without someone having to push a button or other hands-on actions that fire and event.

Is there some way to get an event to fire on a timer that continuously runs? How would you code that?

 

[RickXL]

Try using the Get External Data option on the Data tab. Choose From Text and point it at any convenient text file. Set the field delimiters appropriately. A text file with not much in it (e.g a single string) will be easiest.

When the Import Data dialog pops up next, select Properties. In there, uncheck the "Prompt for the filename on refresh" option and set the "Refresh entry every nn minutes" option, set nn to 5, and OK alol the way out.

Then, at the prescribed intervals, Excel will go to the text file, read it in and recalculate the worksheet.

A thought: How does Excel communicate with PI? Could the Get External Data option be used for that? If so, then you would not need the dummy text file.

Regards,

 

[guntoter]

Rick, there is an update button on the PI menu in Excel, but it only works when clicked. No timer as far as I know.

 

[guntoter]

I was able to find code that does this. Here is what someone posted on a website:

Dim SchedRecalc As Date

Sub Recalc()
'Change specific cells
Range("A1").Value = Format(Now, "dd-mmm-yy") 'I changed this line and the next line to fit my spreadsheet
Range("A2").Value = Format(Time, "hh:mm:ss AM/PM")

'or use the following line if you have a cell you wish to update
Range("A3").Calculate ' I used this line instead of the ones above.

Call StartTime ' need to keep calling the timer, as the ontime only runs once
End Sub

Sub StartTime()
SchedRecalc = Now + TimeValue("00:00:10") 'you can edit the time of the timer here, such as ("00:05:30) is 5 min. and 30 sec.
Application.OnTime SchedRecalc, "Recalc"
End Sub

Sub EndTime()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, _
Procedure:="Recalc", Schedule:=False
End Sub

and, to make sure it stops, in the This Workbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StartTime
End Sub