Saving a file with date and time as filename

[AAn]

I would like to create a macro to save a worksheet as a file with date and
time as filename.

 

[Joel]

Sub SaveSht()
Folder = ThisWorkbook.Path
OldName = ThisWorkbook.Name
'remove extension
OldName = Left(OldName, InStr(OldName, ".") - 1)
NewName = OldName & Format(Date, "_MM_DD_YY") & ".xls"
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=Folder & "\" & NewName

ActiveWorkbook.Close

End Sub

 

[Simka]

Hi,

I wrote this code a few years ago and have been using it ever since, it
works well except I would like a way of changing the current directory within
the code but I have to use the 'File', 'Save As', to change the current
directory (maybe someone knows a good way to change the curdir within the
code):

----------------------------------------------------------------------

Sub SaveAsNewFileName()

'Save the current file with a date and time file name as '2008-01-16 @
10-48-57.xls'

Filname1 = Now
SavedFilename = Format(Filname1, "yyyy-mm-dd @ hh-mm-ss")
Currpath = CurDir

Response = MsgBox("Current path is: " & Currpath & " is this OK?",
vbYesNo)
If Response = vbNo Then
MsgBox "Change to the Appropriate Drive and Folder before continuing.."
End
End If

oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Saving File..." & SavedFilename
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar
ActiveWorkbook.SaveAs Filename:=SavedFilename, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False

MsgBox "File Saved as: " & Currpath & "\" & SavedFilename

End Sub

 

[Dave Peterson]

Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

John Walkenbach has one at:
http://spreadsheetpage.com/index.php/tip/selecting_a_directory/

If you and all your users are running xl2002+, take a look at VBA's help for:
application.filedialog(msoFileDialogFolderPicker)

 

[Joel]

See if you like this. I used the GetSaveAsFilename method selecting your
filename as the defult path and filename. then the user can choose any
directory or filename he chooses. I also put in the code if the person hits
cancel on the pop up window it will default back to your original filename.


Sub SaveAsNewFileName()

'Save the current file with a date and time file name as
'2008-01-16 @10-48-57.xls'

Filname1 = Now
SavedFilename = Format(Filname1, "yyyy-mm-dd @ hh-mm-ss")
Currpath = CurDir
fileSaveName = Application.GetSaveAsFilename( _
InitialFileName:=Currpath & SavedFilename, _
fileFilter:="Text Files (*.xls),*.xls")

If fileSaveName = False Then
fileSaveName = Currpath & "\" & SavedFilename
End If


oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Saving File..." & SavedFilename
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar
ActiveWorkbook.SaveAs _
Filename:=fileSaveName, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

MsgBox "File Saved as: " & fileSaveName

End Sub

 

[Simka]

Good try Joel but,

I have noticed a few things that need amending:

1. Open a new worksheet and attempt to save the file using this routine and
instead of pressing 'Save', press 'Cancel', the file saves as oppossed to
cancel the routine.

2. When I run this routin and see the 'save' dialogue box, have a look in
the Filetype textbox, on my Excel it shows 'Save as Type: Text Files (*.xls)'
- this should be: 'Save as Type: Microsoft Office Excel Workbook (*.xls)' (or
doesn't it matter).

3. If I run this routine several times, on my Excel the routine crashes
after several attemps to save.

Simka

 

[Joel]

I started with a Text SAVEAS and modified it. It just a string for the user
to see

from
fileFilter:="Text Files (*.xls),*.xls")
to
fileFilter:="Excel Files (*.xls),*.xls")


I didn't know what you wanted to do if the operator hit cancel. to quit
make change below

from
If fileSaveName = False Then
fileSaveName = Currpath & "\" & SavedFilename
End If
to
If fileSaveName = False Then
msgbox("Cannot Save file - Exiting Sub")
exit sub
End If

 

[Simka]

This may have solved the SaveAs part and as regards to what should the
routine do if the user pressed 'Cancel'? I would have thought that the
routine should cancel the save. But instead of:

If fileSaveName = False Then
msgbox("Cannot Save file - Exiting Sub")
exit sub
End If


I would have written:

If fileSaveName = False Then
msgbox("File not Saved - Exiting Sub")
exit sub
End If

Try running your routine over and over 8 or more times, it seems to change
folders by itself and occassionally crashes. I still prefer my routine, as
soon as you change the curdir using the SaveAs/Cancel it saves it over and
over in the chosen folder changing the filename at each save + mine doesn't
crash and it's possible to exit out without saving if the user selects No at
the curdir dialogue.

Simka.