Question: Using Excel 2002 SP2, I use VBA to drive a database of currently 15,000 records which automates a number of tasks such as letters, faxes, etc. Problem is, is that I don't know how to create an autosave function. I've integrated a save button instead but would like it to autosave after, say, 10 minutes. The only thing I can think of is to give the workbook shared access. I don't want to do that. Any assistance will be appreciated. Cheers!
Autosave using VBA after n minutes
Hot Topic
Microsoft ISV
- printing .doc, .xls, and .pdf files from a list of filenames using an excel vba macro
- Autosave using VBA after n minutes
- Advanced Excel VBA SEVERAL QUESTIONS
- Business Scorecard Manager 2005 database problem
- export lots of Access stored queries
- xp style to vba forms
- cannot save file as
- Remove user from (Folder-)ACL
- Close excel
- Runtime error 1004 when adding a seriescole to a chart
Autosave using VBA after n minutes
Stephane S
How about using Application.OnTime That's the way I handle autosaves in my application. Here's the way I've done it:
Sub StartSaveTimer(Optional iRunIntervalSeconds As Integer = cRunIntervalSeconds)
dTimerRunWhen = Now + TimeSerial(0, 0, iRunIntervalSeconds)
Application.OnTime earliesttime:=dTimerRunWhen, procedure:=cRunWhatProc, _
schedule:=True
End Sub
When I want to start the Autosave, I call StartSaveTimer with the interval I want it to save (in your case it would be 600 seconds). Then I ask Excel to run the procedure stored in cRunWhatProc at the specified time. In the procedure I save the workbook and schedule another save. Note: be sure to save the value of dTimerRunWhen, because that's the only way to stop a scheduled event!:)
Hope that helps
TOM A.
lottoman2000
Do you need to create your own, or could you use an existing add-in I use a utility called AutoSafe, by Jan Karel Pieterse. It's a free download from http://jkp-ads.com. You can set it to save at specified intervals in a specified directory. It's much less obtrusive than Excel's autosave functionality.
If this doesn't work for you, check out the Application.OnAction method, and use it to trigger a save of the appropriate file.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
Lowell M
That's what I meant, Application.OnTime (not Application.OnAction).
- Jon