Save a file with the date and time stamp.

[gjsala]

I have the following code:
ActiveWorkbook.SaveAs "C:\test\" & .xls"

I would like to have the VBA code save the date and time to the file name when the macro is run. For example: “home_4_30_2010_2:45:00 pm.xls” onto the c drive.

Thanks in advance!

 

[SkipVought]

Hi,

Check out the Format function...

'example: "home_4_30_2010_2:45:00 pm.xls" 
sFileName = "home_" & Format(Now, "d_m_yyyy_h:nn:ss am/pm") & ".xls"

Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]

 

[gjsala]

Skip,
Thanks for the quick response! I tried put your answer into my code and it didn't save to the c drive here is what I have:

Dim sFileName as string
ActiveWorkbook.SaveAs "C:\test\" & sFileName & ".xls"

Any ideas?
Thanks.

 

[SkipVought]

what is in sFileName???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gjsala]

I would like this:
Format(Now, "d_m_yyyy_h:mm:ss am/pm")
This would represent the date and time the macro was run.

 

[gjsala]

I found this worked:

ActiveWorkbook.SaveAs FileName:="C:\test\" & _
Format(Now(), "mm_dd_yyyy_hh_mm_AM/PM"), FileFormat:=xlNormal, Password:="", _
WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

 

[fumei]

yes...it would work because (as Skip pointed out) you finally gave it a value.

Dim sFileName as string

sFileName =  Format(Now(), "mm_dd_yyyy_hh_mm_AM/PM")

'  NOW it will work
ActiveWorkbook.SaveAs "C:\test\" & sFileName & ".xls"

Gerry