How to do a "Save As" in vba code, saving my current Excel workbook with datestamp?

How to Save Your Excel Workbook with a Datestamp Using VBA

Learn to automate saving your Excel workbook with a unique datestamp and timestamp, ensuring an organized archive of your work.

Archiving different versions of your Excel workbooks is a common requirement, especially for critical data or project snapshots. Manually saving with a unique name each time can be tedious and prone to errors. This article guides you through creating a VBA macro that automates this process, appending the current date and time to your workbook's filename, ensuring each saved version is distinct and easily identifiable.

Understanding the Core VBA Concepts

To implement the "Save As" functionality with a datestamp, we'll leverage several key VBA objects and methods:

• ThisWorkbook: Refers to the workbook where the VBA code is running.
• Application.ActiveWorkbook: Refers to the currently active workbook. While similar to ThisWorkbook, it's safer to use ThisWorkbook if you want to ensure the macro always affects the workbook containing the code.
• SaveAs: A method of the Workbook object that allows you to save the workbook with a new name or format.
• Format(): A VBA function used to format dates and times into a string, which is crucial for creating a clean datestamp for the filename.
• Environ("UserProfile"): Retrieves the path to the current user's profile directory, useful for constructing dynamic save paths.

The goal is to construct a new filename that includes the original name, a separator, and the formatted date and time. We also need to specify the file format (xlOpenXMLWorkbook for .xlsx) and handle potential issues like ensuring the target folder exists.

Sub SaveWorkbookWithDatestamp()
    Dim ws As Workbook
    Set ws = ThisWorkbook
    
    Dim newFileName As String
    Dim savePath As String
    
    ' Define the path where you want to save the file
    ' You can change "C:\YourFolder\" to your desired path
    ' Alternatively, use ws.Path for the current workbook's path
    savePath = ws.Path & "\Archive\" ' Saves in a subfolder called Archive
    
    ' Ensure the folder exists. If not, create it.
    If Dir(savePath, vbDirectory) = "" Then
        MkDir savePath
    End If
    
    ' Construct the new filename
    ' Format: OriginalFileName_YYYYMMDD_HHMMSS.xlsx
    newFileName = ws.Name & "_" & Format(Now, "YYYYMMDD_HHMMSS") & ".xlsx"
    
    ' Remove the original .xlsm or .xlsx extension from ThisWorkbook.Name
    ' This is important if ThisWorkbook.Name already includes the extension
    If InStr(newFileName, ".xlsm") > 0 Then
        newFileName = Replace(newFileName, ".xlsm", "")
    ElseIf InStr(newFileName, ".xlsx") > 0 Then
        newFileName = Replace(newFileName, ".xlsx", "")
    End If
    
    ' Add the correct extension for the new file format
    newFileName = newFileName & ".xlsx"
    
    ' Save the workbook with the new name and path
    ws.SaveAs Filename:=savePath & newFileName, FileFormat:=xlOpenXMLWorkbook
    
    MsgBox "Workbook saved as: " & savePath & newFileName, vbInformation
End Sub

VBA code to save the current workbook with a datestamp in a specified folder.

Breaking Down the Code and Customization

Let's examine the key parts of the provided VBA code and how you can customize it for your specific needs.

1. Defining the Save Path

The savePath variable determines where your archived files will be stored. You have several options:

• Current Workbook's Path: savePath = ThisWorkbook.Path & "\Archive\" will create an "Archive" subfolder within the directory of the current workbook.
• Specific Fixed Path: savePath = "C:\MyExcelArchives\" allows you to define a static folder.
• User's Documents Folder: savePath = Environ("UserProfile") & "\Documents\Excel Archives\" dynamically finds the user's Documents folder. This is often a good default for multi-user environments.

2. Formatting the Datestamp

The Format(Now, "YYYYMMDD_HHMMSS") part is crucial. Now returns the current date and time. The format string "YYYYMMDD_HHMMSS" specifies how this date and time should appear in the filename:

• YYYY: Four-digit year
• MM: Two-digit month
• DD: Two-digit day
• HH: Two-digit hour (24-hour format)
• MM: Two-digit minute
• SS: Two-digit second

You can adjust this format. For example, "YYYY-MM-DD" would result in 2023-10-27.

3. Handling File Extensions

The code includes logic to remove existing .xlsm or .xlsx extensions before adding the new .xlsx extension. This ensures that if your original file is MyReport.xlsm, the saved file becomes MyReport_YYYYMMDD_HHMMSS.xlsx, not MyReport.xlsm_YYYYMMDD_HHMMSS.xlsx.

4. FileFormat Parameter

FileFormat:=xlOpenXMLWorkbook specifies that the file should be saved as an .xlsx file. Other common formats include:

• xlOpenXMLWorkbookMacroEnabled: For .xlsm (macro-enabled workbook)
• xlExcel8: For .xls (Excel 97-2003 workbook)
• xlCSV: For .csv (Comma Separated Values)

5. Error Handling (Advanced)

For production environments, you might want to add error handling using On Error GoTo ErrorHandler to gracefully manage situations like the save path being invalid or the file being open elsewhere.

Workflow of the VBA Save As with Datestamp macro.

Integrating the Macro into Your Workbook

Once you have the VBA code, you need to place it in your Excel workbook so it can be executed.

Steps to Add the VBA Code:

1. Open the VBA Editor: Press Alt + F11 to open the Visual Basic for Applications editor.
2. Insert a Module: In the VBA editor, right-click on your workbook name in the Project Explorer (usually on the left pane), then choose Insert > Module.
3. Paste the Code: Copy the Sub SaveWorkbookWithDatestamp() code from above and paste it into the newly created module.
4. Save Your Workbook: Save your Excel workbook as a "Macro-Enabled Workbook" (.xlsm) to preserve the VBA code. Go to File > Save As, choose a location, and select "Excel Macro-Enabled Workbook (*.xlsm)" from the "Save as type" dropdown.

Running the Macro:

• From VBA Editor: Place your cursor anywhere inside the SaveWorkbookWithDatestamp sub-routine and press F5.
• From Developer Tab: Go to the Developer tab in Excel, click Macros, select SaveWorkbookWithDatestamp, and click Run.
• Assign to a Button/Shape: You can insert a button or shape on your worksheet, right-click it, choose Assign Macro, and select SaveWorkbookWithDatestamp.