How to save Excel file with specific name and current date with VBA?

by

In this short tutorial we will learn how you can use Visual Basic for Applications (VBA) to automate the saving of your Excel 365 spreadsheet. Our code will assign a custom file name, consisting of a prefix concatenated with the current date and time.

Macro to save workbook with VBA

Step #1: Prepare your Excel workbook

First off, create the Excel spreadsheet you would like to save. Ensure that the Developer tab is available in your Excel App, as you will use it in the next step in order to insert your VBA module and code.

Step #2: Add a new VBA module

Next we’ll add a VBA module by following these steps:

  • In your Excel workbook, hit the Developer button in the Ribbon.
  • Then hit the Visual Basic button (alternatively hit ALT + F11).
  • In the Project Explorer add a new Module (or use an existing Module).

Step #3: Save the spreadsheet with VBA

  • Add the following code into the VBA Module you have identified:
Sub Save_Spreadsheet_With_Name_Date()
    Dim CurrentDate As String
    Dim FilePath As String
    Dim FileName As String
    
    CurrentDate = Format(Date, "yyyy_mm_dd")
    
    ' change the file directory and file name as needed.
    FilePath = "C:\MyDocs\_"
    FileName = "MyWorkbook_"
    
    ActiveWorkbook.SaveAs FilePath & FileName & CurrentDate & ".xlsx"
    MsgBox("File create succesfully")
End Sub
  • Save your code.

Step #4: Check your work

  • Run you code.
  • Using the Windows File Explorer, navigate to the file directory in which you wrote your file.
  • Last, go ahead and check the file name to see if it fits your needs.