Automating Data Refesh In Excel 97

Posted by Christopher Astill on March 06, 2001 2:51 AM

I would like to be able to refresh data in an excel 97 spreadsheet which is linked to an access database. I would like to be able to automate this process so it occurs every 10 minutes. I am fairly new to excel but I believe you should be able to do this using a macro. Any help would be much appreciated.

Posted by Dave Hawley on March 06, 2001 5:46 AM

Hi Christopher

You can use the OnTime Event for this. But first you will need to trigger it when you open the Workbook.

Right click on the sheet picture, top left next to "File" and select "View Code". Paste in this code.

Private Sub Workbook_Open()
Run "TimerSet"
End Sub

Then in a normal Module put:

Sub TimerSet()
Application.OnTime Now + TimeValue("00:10:00"), "TimerSet"
'Your code
End Sub

This will re-run itself every ten minutes.

Dave

OzGrid Business Applications

Posted by Celia on March 06, 2001 2:59 PM

Christopher
Please note, however, the timer will continue to run after the workbook is closed and will re-open the workbook when the next scheduled time arrives. If you need to "switch-off" the timer, use the following macro :-

Sub StopTimer()
On Error Resume Next
Application.OnTime + TimeValue("00:10:00"), "TimerSet", , False
End Sub

To switch-off automatically when the workbook is closed :-

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopTimer
End Sub

Celia

Posted by Randy Evans on March 07, 2001 12:27 AM

Just a thought,
Instead of turning off the timer could you put in the code the actual times that you wanted the refreshes done. For example 2130, 2230, 2330, 0800, 0900. Let's say you closed the workbook around midnight each night. Or it got closed by mistake. Would the 0800 refresh time(or any time for that matter)automatically open the workbook back up and continue your refresh times throughout the day? It sounds like this would work. In this case actual times might be preferable to time intervals?

Posted by Celia on March 07, 2001 1:54 AM

It is possible to specify actual times but the on-time event is switched-off if Excel is closed and would only be switched back on when the workbook containing the code is opened. Anyway, it is improbable that there is a necessity to update the workbook if it is closed and not in use.
If required, I suppose the Task Scheduler could be used to open Excel at a specified time each day, and the on-time workbook either kept in the Excel start folder or opened by a macro from another file in the XLStart folder.
Celia

Posted by Christopher Astill on March 08, 2001 12:03 AM

Posted by Christopher Astill on March 08, 2001 12:10 AM

Thanks Celia,
I tried your code at work today and everything worked fine. We have been working on a project to capture production data in real time. WE have a real time vision application which is currently linked to an excel speadsheet. This automatic refeshing will save us having to do this function manually. Your assistance in this has been extremely appreciated.

Cheers
Chris