Newbie, how to save file with date and time as filename

[Guest]

I would like to be able to save a file with the name of the file to be the
current date and time.
Any help?

 

[David McRitchie]

Hi Michael,
See backup on demand
http://www.mvps.org/dmcritchie/excel/backup.htm#demand

be sure to format so that you have year, then month, then day
so that you can quickly find the file alphabetically in the directory.

 

[Gary Keramidas]

one way, assuming a date is in a1 using =now()
Sub test()

Dim wb As String
Dim fPath As String
Dim dtStr As String
Dim fName As String
Dim ws As Worksheet

Set ws = Worksheets("sheet1")
wb = ThisWorkbook.Name
fPath = ThisWorkbook.Path & "\"
fName = Format(ws.Range("A1").Value, "mmddyy hhmm")
Workbooks(wb).SaveAs Filename:=fPath & fName & ".xls"
End Sub

 

[Guest]

Micheal,

This works for me in project i'm currently working on. You can use a command
button to activate it if you like -

CommandButton1_Click()

ActiveWorkbook.SaveCopyAs "C:\{My FolderName}\{My FileName} _
& "." & Format(Now, "dd-mmm-yyyy.hh-mm-ss") & ".xls"

End Sub

Replace the bracketed {} with your own folder and file names.

hth
Mark

 

[Guest]

Mark,
Thats cool, I got it to run as a macro without the button thing (dont get
that) but it saves a copy but not the one I am working in.
So if I am working on book1 it will save a copy with the date and time but
it will still say book1.
This might just be a small tweak to do so I thought I would ask some more.
THANKS

 

[Guest]

Cool, dont get the button part but got the rest to work. Changed it to saveas
instead of copy.
THNAKS

 

[Guest]

I like this better.
ActiveWorkbook.SaveCopyAs "C:\{My FolderName}\{My FileName} _
& "." & Format(Now, "yyyy-mm-dd.hh-mm-ss") & ".xls"