Share

Yes, you can create a macro that does this.  You can copy and paste the code below to add the current date and time to the file name of a file. 

On the Developer tab, click on Visual Basic.

 

When the Visual Basic window opens, click on Insert  > Module.

When the new module appears, copy and past the following code:

Sub TimeStampSave()

‘Will create a routine to add the date and time to the name of a file

‘The following 5 rows declare the variables

Dim varWB As Workbook
Dim varSTR As String
Dim varOldName As String
Dim varDATE As String
Dim varFNAME As String

‘This section will add the current time-stamp to the file name

Set varWB = ActiveWorkbook
varOldName = varWB.Name
varSTR = Left(varOldName, Len(varOldName) – 5)
varDATE = Format(Now, “yyyy-mm-dd hh-mm”)

‘This section will save the file as macro-enabled or not

If Right(varOldName, 4) = “xlsm” Then
varFNAME = Application.GetSaveAsFilename(varSTR & ” ” & varDATE, “Excel Macro-Enabled Workbook (*.xlsm),*.xlsm”)
Else
varFNAME = Application.GetSaveAsFilename(varSTR & ” ” & varDATE, “Excel Workbook (*.xlsx),*.xlsx”)
End If

varWB.SaveAs (varFNAME)
End Sub

 

When you paste the code into the module, it will look something like this:

Don’t worry if your spacing looks different.  That won’t matter.  The lines in green (beginning with apostrophes) are comments.

Now you can close the Visual Basic Editor and go back to your Excel file.

To run the macro, select Macros from the Developer tab, select TimeStampSave and click Run.

It will append the current time stamp to the name of the file.

If you want to make this macro always available, save it to Personal.xlsb.

 

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Share

shares