Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a file every fifteen minutes
Hi
Can someone help me with the code to save an excel worksheet every fifteen minutes? I will need some sort of a sleep command - what can I use in excel? What is the syntax? Thanks a bunch Priyanka |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a file every fifteen minutes
hi Priyanka,
something like that in a module. There might be other ways as well. Sub StartIt() Application.OnTime Now + TimeValue("00:00:03"), "SaveIt" ' every third second for testing End Sub Sub SaveIt() If ActiveWorkbook.Saved = False Then ActiveWorkbook.Save MsgBox "saved" ' for testing End If StartIt End Sub -- Greetings from Bavaria, Germany Helmut Weber, MVP WordVBA Vista Small Business, Office XP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a file every fifteen minutes
From Excel:
Tools Options Save check autosave and set the time to 15 minutes. From VBA: Application.AutoRecover.Time = 15 This avoids having to use an OnTime Event macro -- Gary''s Student - gsnu200762 "Priyanka" wrote: Hi Can someone help me with the code to save an excel worksheet every fifteen minutes? I will need some sort of a sleep command - what can I use in excel? What is the syntax? Thanks a bunch Priyanka |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a file every fifteen minutes
It looks like you're using AutoRecover instead of AutoSave.
Saved from a previous post: xl2k and below came with an optional addin called AutoSave.xla. It could be set to save every x minutes (user selectable). And it just saves the file at those intervals. xl2002+ comes with something called autorecovery. It's also optional, but if the user turns it on, it saves a copy of that workbook in a special location (also user selectable). If windows or excel crashes, then the next time excel opens, it notices that there's a file in that location. Excel prompts the user to see if he/she wants to recover that file that was saved when excel/windows crashed. This autorecovery feature isn't used for the same purpose as AutoSave. You may be interested in an addin that Jan Karel Pieterse (works in any version) called AutoSafe (note spelling). It doesn't overwrite the existing workbook when it saves. It saves to a user selectable folder. And when it's done, it either deletes these backups (or puts them in the recycle bin). And the user can always restore the backups from the recycle bin. http://www.jkp-ads.com/Download.htm (look for AutoSafe.zip, not autosafeVBE.zip, for your purposes.) If you really want autosave... Gord Dibben posted this: Autosave.xla from Office 2000 or 97 will work with Excel 2002 or 2003. If you have a previous copy, move it to your Office\Library. To download the 97 version see here........ http://www.stat.jmu.edu/trep/Marchat/sp2001/Library.htm Gary''s Student wrote: From Excel: Tools Options Save check autosave and set the time to 15 minutes. From VBA: Application.AutoRecover.Time = 15 This avoids having to use an OnTime Event macro -- Gary''s Student - gsnu200762 "Priyanka" wrote: Hi Can someone help me with the code to save an excel worksheet every fifteen minutes? I will need some sort of a sleep command - what can I use in excel? What is the syntax? Thanks a bunch Priyanka -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a file every fifteen minutes
Thank you for the correction & clarification
-- Gary''s Student - gsnu200762 "Dave Peterson" wrote: It looks like you're using AutoRecover instead of AutoSave. Saved from a previous post: xl2k and below came with an optional addin called AutoSave.xla. It could be set to save every x minutes (user selectable). And it just saves the file at those intervals. xl2002+ comes with something called autorecovery. It's also optional, but if the user turns it on, it saves a copy of that workbook in a special location (also user selectable). If windows or excel crashes, then the next time excel opens, it notices that there's a file in that location. Excel prompts the user to see if he/she wants to recover that file that was saved when excel/windows crashed. This autorecovery feature isn't used for the same purpose as AutoSave. You may be interested in an addin that Jan Karel Pieterse (works in any version) called AutoSafe (note spelling). It doesn't overwrite the existing workbook when it saves. It saves to a user selectable folder. And when it's done, it either deletes these backups (or puts them in the recycle bin). And the user can always restore the backups from the recycle bin. http://www.jkp-ads.com/Download.htm (look for AutoSafe.zip, not autosafeVBE.zip, for your purposes.) If you really want autosave... Gord Dibben posted this: Autosave.xla from Office 2000 or 97 will work with Excel 2002 or 2003. If you have a previous copy, move it to your Office\Library. To download the 97 version see here........ http://www.stat.jmu.edu/trep/Marchat/sp2001/Library.htm Gary''s Student wrote: From Excel: Tools Options Save check autosave and set the time to 15 minutes. From VBA: Application.AutoRecover.Time = 15 This avoids having to use an OnTime Event macro -- Gary''s Student - gsnu200762 "Priyanka" wrote: Hi Can someone help me with the code to save an excel worksheet every fifteen minutes? I will need some sort of a sleep command - what can I use in excel? What is the syntax? Thanks a bunch Priyanka -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a file every fifteen minutes
Thanks a whole lot Helmut. I used your code. I didn't use auto-recover or
save as I didn't get the difference. But now I have a new problem. The thing is - I am writing a macro does the following: 1. Copies three rows (which are imported every 15 minutes from a website) of a worksheet to another worksheet. Since the data is refreshed constantly, the second worksheet maintains a 'history' of data. 2. Saves the second worksheet after copying. So these are the two procedures i wrote - one for Step 1 and the other that refers to this macro. THIS SUB COPIES THREE ROWS FROM ONE SHEET INTO ANOTHER HISTORY SHEET. Sub Macro1() ' ' Keyboard Shortcut: Ctrl+i ' Sheets("Sheet1").Select Range("A1").Select Selection.End(xlUp).Select Range("A2").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Sheet2").Select Range("A1").Select Selection.End(xlDown).Select Selection.End(xlDown).Select Selection.End(xlUp).Select Dim a As String Dim b As Integer b = InStr(2, ActiveCell.Address, "$") a = "$a$" + Trim(Str(Val(Mid(ActiveCell.Address, b + 1, 20)) + 1)) MsgBox "Total '" & Trim(Str(b)) & "' Records Transfered To History" Range(a).Select ActiveSheet.Paste Range("A2").Select Sheets("Sheet1").Select Range("A5").Select Application.CutCopyMode = False Range("A4").Select End Sub THIS SUB CALLS THE PREVIOUS SUB AND THEN SAVES DATA Sub StartIt() Do While Time < TimeValue("4:50PM") Application.OnTime Now + TimeValue("00:15:00"), "Macro1" If ActiveWorkbook.Saved = False Then ActiveWorkbook.Save MsgBox "saved" ' for testing End If Loop End Sub Now the problem is, the computer hangs when i run this macro. The data file is fairly big. Right now the history file has early 1000 rows. can anyone please please help. I am thoroughly confused as the first sub was written by someone else. Priyanka |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a file every fifteen minutes
Hi Priyanka,
Sub StartIt() Do While Time < TimeValue("4:50PM") .... Loop End Sub Now the problem is, the computer hangs when i run this macro. "Do While Time < TimeValue("4:50PM")" is a deadly sin. It makes the computer run the code uninterruptedly until "4:50PM". If you want code which executes every 15 Minutes after "4:50PM" you might put "if timevalue ("4:50PM")" in Macro1. And there is the call to startit (again) missing as well in Macro1. Programming with times is a special challenge. I think your code would stop at midnight, by the way. -- Greetings from Bavaria, Germany Helmut Weber, MVP WordVBA Vista Small Business, Office XP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically saving a file every 5 minutes | Excel Programming | |||
Saving every 10 minutes | Excel Discussion (Misc queries) | |||
Excel 2003 Undo greys out after few minutes - Not saving | Excel Discussion (Misc queries) | |||
how can convert 15 to Fifteen | Excel Worksheet Functions | |||
Timesheet /sum client fifteen minutes intervals activity | Excel Programming |