How do I automatically save an Excel Worksheet?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Anyone know if there is a way to automatically save an Excell Worksheet say
every 5 minutes? I'm not refering to Auto Recovery. I need an actual save.
 
Hi JE,

This doesn't quiet do what I'm wanting. This appears to make backup copies
with a different name which is what I understand the Auto Restore does. I
want to be able to automatically save the file with the same file name in the
same director.

Here is the scenario I'm running. I've created a report in Excell using Sql
quaries to automaticaly update the information everly 1 minute. I have saved
the report as an html file and want this to update every minute as well.
However, the file only updates once I have opened refreshed the data and
saved it in Excell. I can automatically refresh the data but is there a way
that I can automate the save?

Thanks,
 
Not quite, AutoRestore saves a temp file, which is deleted when XL shuts
down normally.

One way to accomplish what you want is to put something like this in the
ThisWorkbook module of your workbook.

Private Sub Workbook_Open()
AutoSave
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
AutoSave Terminate:=True
End Sub


And put this in a regular code module:

Public Sub AutoSave( _
Optional Interval As Long = 5, _
Optional Terminate As Boolean = False)
Static dNextTime As Double
If Terminate Then
Application.OnTime _
EarliestTime:=dNextTime, _
Procedure:="AutoSave", _
Schedule:=False
Else
On Error Resume Next
If Not dNextTime = 0 Then ThisWorkbook.Save
On Error GoTo 0
dNextTime = Now + TimeSerial(0, Interval, 0)
Application.OnTime _
EarliestTime:=dNextTime, _
Procedure:="AutoSave", _
Schedule:=True
End If
End Sub
 
Back
Top