How to create a unique file name with date and time stamps using a macro. Watch the video below:
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:
Further Reading: