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" wrote:

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
---------------------------------------------------------------------------
See what you think....

Simka








"AAn" wrote:

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