Run a task every minute

On my PC I have an Excel sheet that I have open all day for changes. I would like to copy every minute to another file on an external network hard drive so another person can see my latest changes. I currently have a scheduled DOS task that does this, but limited to once a day. The PC Task Schedular doesn't appear to have periods less than once a day either. Does any body have a suggestion?

 

Haven't tested this but it should autosave at the desired interval and include a timestamp in the filename. Include it as a module in your open workbook.

Public saveTime As Date

Sub AutoSaveAs()
saveTime = Time + TimeValue( "00:05:00 ") ' set this to whatever interval you want
With Application
.OnTime saveTime, "AutoSaveAs"
.EnableEvents = False
.DisplayAlerts = False
ThisWorkbook.SaveAs "FilePath&NameHere" & saveTime & ".xlsx" ' make it xlsm if you want it to be a macro enabled file.
.EnableEvents = True
End With
End Sub

 

Thanks dnmacleod.
Before I load this I have questions...
If I make it Public, when does it get initially launched?
If I make it a macro, does it become manual?
Does the timestamp at the end of the file name make it a unique file (not overlay)?

 

OOPS

That's what happens when you post code without checking it first....

Re-write.....
-----------------------------------------------------------

Public saveTime As Date

Sub AutoSaveAs()
Dim dt As String
dt = Format(CStr(Now), "mm_dd_hh_mm ") ' Edit the Date/Time format as you see fit
saveTime = Time + TimeValue( "00:05:00 ") ' set this to whatever interval you want - in this case 5 mins
With Application
.OnTime saveTime, "AutoSaveAs "
.EnableEvents = False
.DisplayAlerts = False
ActiveWorkbook.SaveAs "C:\Path\To\File\name_" & dt & ".xlsm"
.EnableEvents = True
End With
End Sub

If you want to auto run the macro on opening the workbook, place a call to AutoSaveAs in ThisWorkbook under WorkBook Open thus:

Private Sub Workbook_Open()

Call AutoSaveAs

End Sub

If you want to manually run the macro initially, don't include the Call.

The timestamp will save it as a new unique file every time.

Also, the macro will continue to run after the workbook has been closed until Excel is fully closed down. When Excel is restarted, the macro will not restart until the workbook is re-opened.

 

Add the following sub underneath the auto start call in the ThisWorkbook module to stop the macro when you close the workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime saveTime, "AutoSaveAs ", Schedule:=False
End Sub

 

dnmacleod,
This method has more hoops to jump through than I feel necessary for my needs, so I think for business purposes I'll try some other basic system tools for now.
Please understand that I greatly appreciate your contribution and will experiment with it in a non-business environment. If I get it working I may decide to use it in a number of applications.

clinet

 

Thats ok. Just one thing - I thought you wanted it saved as a unique file every time, thats why I included the timestamp. If you simply want to overwrite the remote file every time, simply change the line:

ActiveWorkbook.SaveAs "C:\Path\To\File\name_" & dt & ".xlsm" to
ActiveWorkbook.SaveAs "C:\Path\To\File\name.xlsm "