Creating excel file using VBA - File name to contain the current date and time

[Rampage123]

Hi all,

I am running a VBA based tool that I have made which calculate certain output.

Now, the problem is I run this tool multiple times a day and I would like to save each output in a separate excel file.

Is it possible to write a macro to create a separate excel file that contains current system date and time and export my output to that file each time so that I have each version of output saved.

If yes, can somebody share the code for such an exercise. I have searched online a bit, but with no use.

Thanks in advance.

[JKwan]

something like this?

Sub CreateDateTimeFileName()
    Dim sFileName As String
    
    sFileName = Format(Now(), "mmddyyyy") & " - " & Format(Now(), "hhss")
    
    MsgBox sFileName
End Sub

[Pasi12]

Hello Jkwan,

Not sure exactly you looking for but:

Here is how I have it up in my MSaccess vba---> strFile = "C:\Users\USERNAME\Desktop\Reports\FileNAME-" & Format(Date, "mm-dd-yyyy") & ".xls"

When I run my report it outputs the file name including todays timestamp into my directory.

fill in your user name and file name extension.

Pasi.

[Rampage123]

something like this?

Sub CreateDateTimeFileName()
    Dim sFileName As String
    
    sFileName = Format(Now(), "mmddyyyy") & " - " & Format(Now(), "hhss")
    
    MsgBox sFileName
End Sub

Thanks JKwan.

But, where is the file getting created? I can't find any excel file on my system with that name. The message box is appearing though

[GTO]

Where is the code going to be housed? In the workbook that you want multiple copies of?

Also - what year of Excel are you using, and what format do you save in (.xls, .xlsx, .xlsm, ...)?

[Rampage123]

Dear all,

Thanks for the help on this. I have figured out how to do this.

Regards