PDA

View Full Version : [SOLVED] Creating excel file using VBA - File name to contain the current date and time



Rampage123
01-30-2014, 07:42 AM
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
01-30-2014, 09:47 AM
something like this?


Sub CreateDateTimeFileName()
Dim sFileName As String

sFileName = Format(Now(), "mmddyyyy") & " - " & Format(Now(), "hhss")

MsgBox sFileName
End Sub

Pasi12
01-30-2014, 10:06 AM
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
01-30-2014, 11:42 PM
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
01-30-2014, 11:55 PM
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
01-31-2014, 12:29 AM
Dear all,

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

Regards