Saving particular file in two different location

[R J Solanki]

Hi,
I am working on master file in which i have to make entry on daily basis and take back up of such excel (xls) file (MS excel 2007) on daily basis. Now, i got idea from this forum, can it be possible to save the same excel file in two different location so in case if one file is corrupted i can access to file saved in another location
Note : I want to make such setting only for my master file other excel file should be saved as per normal or default setting of excel

Thanks

 

[Jon Peltier]

You could write a little macro to do this for you. Press Alt+F11 to open the VBA Editor. Find the workbook in the Project Explorer pane (usually on the left of the window, if it's not visible, go to View menu > Project Explorer), and click the little plus sign to open the project and see everything in it. If there's a "Microsoft Excel Objects" folder, click the little plus sign to open it. Double click the ThisWorkbook object. This opens a code pane that will contain code used only when you try to do something in that workbook, like save it. It will not affect other workbooks.

In the top left dropdown, select Workbook. Delete the Workbook_Open() code shell that appears if it is blank between the Sub and End Sub lines.

In the right dropdown, select BeforeSave. This appears:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub

Add the following to the procedure, of course changing to the backup file name you want to use:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Const sBackupSaveAs As String = "C:\Temp\SpareCopyOfFile.xls"
  
  Application.DisplayAlerts = False
  Me.SaveCopyAs sBackupSaveAs
  Application.DisplayAlerts = True
  
End Sub

When you save the workbook, before the save happens, it runs the Workbook_BeforeSave routine, which saves a copy of the file to the backup file name. The display alerts stuff prevents that warning that a file by that name already exists, do you want to blah blah. When the BeforeSave routine is done, the regular save takes place.