When working with some particularly valuable files, it may be necessary to save intermediate versions of it periodically during the working day in order to be able to return to them if necessary. It is tedious to manually execute the “File – Save As” commands all the time, and you can accidentally click on “Save” instead of “Save As”, burying a large piece of work.
If you have Excel 2010, then in addition to the standard autosave, the version system should work for you – every time you autosave, Excel makes a separate copy of your current file and (even!) When you exit the program and answer the question “Save changes to the file?” still keeps a temporary copy. You can access these temporary copies through the tab File – Details – Versions (File — Properties — Versions):
For my projects, I eventually came to a different solution – I wrote a macro that saves the current book to a specified folder, adding the current date and time to the book name in the DD-MM-YY HH-MM format (for example, My project 12-10-12 07 -35). By periodically running this macro at the key stages of working with a file, I get a list of the x number of file versions of the workbook and, accordingly, I can easily roll back to the desired version in the past.
Open the Visual Basic Editor by selecting on the tab Developer – Visual Basic Editor (Developer — Visual Basic Editor) or by pressing ALT+F11. Insert via menu Insert – Module new empty module and copy the text of this macro there:
Sub Backup_Active_Workbook() Dim x As String strPath = "c:TEMP" 'folder to save the backup On Error Resume Next x = GetAttr(strPath) And 0 If Err = 0 Then ' if the path exists, save a copy of the workbook, adding the date- time strDate = Format(Now, "dd-mm-yy hh-mm") FileNameXls = strPath & "" & "My Project" & " " & strDate & ".xls" ' or xlsm ActiveWorkbook.SaveCopyAs Filename:=FileNameXls Else 'if the path does not exist, display the MsgBox message "The folder "& strPath & " is not available or does not exist!", vbCritical End If End Sub
Naturally, the path to the folder (C:TEMP) and the file name (My project) must be replaced with your own.
If your save folder is located on a network drive, then its address can be registered using the server IP address, for example:
strPath = "192.168.1.1Backup folder"
Another, possibly useful, addition is that the file name may not be constant, but taken from a given cell of the sheet, where it is either entered by the user, or it is automatically formed by formulas (for example, the CONCATENATE function, etc.). Then it will be necessary to slightly correct the following line:
FileNameXls = strPath & "" & Sheets("Лист1").Range("A1").Value & " " & strDate & ".xls"It is assumed that the file name is taken from sheet Sheet1 from cell A1.
- Automatic backup to a specified folder using the PLEX add-on
- What are macros, where to insert macro code in VBA, how to use them?