Saturday, 22 November 2014

How to Create a Backup for Active Workbook with Current Date with Excel Macro

Excel VBA Macro to Create a Back up for Active Workbook with Current Date in Same Location.
Sub Create_Backup()
ActiveWorkbook.SaveCopyAs _
Filename:=ActiveWorkbook.Path & "\" & "BackUp" & "_" & _
Format(Date, "MM-DD-YY") & "_" & ActiveWorkbook.Name
End Sub

In the Same way we can Create a Back up for ThisWorkbook with Current Date as follows :

Sub Create_Backup()
ThisWorkbook.SaveCopyAs _
Filename:=ThisWorkbook.Path & "\" & "BackUp" & "_" & _
Format(Date, "MM-DD-YY") & "_" & ThisWorkbook.Name
End Sub

Tips :
If you want to Save the ActiveWorkbook of different format(97-2003 format) to a desired format(2007 format), use the following file format Codes.

51 = xlOpenXMLWorkbook (2007-2013, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (Macro Enabled Format in 2007-2013, xlsm)
50 = xlExcel12 (Excel Binary Format in 2007-2013 , xlsb)
56 = xlExcel8 (97-2003 Format to Excel 2007-2013, xls)

ActiveWorkbook.SaveAs "C:\Users\YourUserName\Desktop\YourFileName", Fileformat:=52 

It is always better to use the FileFormat Code Numbers instead of the Defined Constants in the code so that Macro will Compile OK .

Excel 97-2003 may won't understand what is the < xlOpenXMLWorkbookMacroEnabled> Constant is.

Help :
Active Workbook means the Workbook which is Currently Active / on which we are currently working.

This Workbook means the Workbook in which we are writing the Macro code.

Sample View of BackUp :

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts