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...g_a_directory/

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

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.

--

Dave Peterson