Saturday, November 22, 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)

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

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

Ex:
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 :




Thanks, TAMATAM

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog