March 1, 2022

Create Unique File Name with Date Time Stamp

How to create a unique file name with date and time stamps using a macro. Watch the video below:

Create a unique file name with data and time stamp using VBA

Earlier we had learnt how to generate unique random numbers using the RND function. Today we will learn how to generate unique numbers using formatted dates and times. We will then concatenate this unique number with a string to construct a unique filename.

The complete VBA or macro code to generate such unique numbers and attaching it to text is given below:

Sub SaveFileUsingUniqueNumber()
Dim Path As String
Dim FileName1 As String

Path = “C:\AddUniqueNumberToFileName\”
FileName1 = Range(“A1”)

ActiveWorkbook.SaveAs Filename:=Path & FileName1 & Format(Date, “ddmmyyyy”) & Format(Time, “hhmmss”) & “.xls”, FileFormat:=xlNormal
End Sub

A few unique files created with the above macro code is shown below as an image:

Create a unique file name with data time stamp using macro

Further Reading:

Workbook.Save method (Excel)