Problem setting directory in xlDialogSaveAs

[Jonathan Crow]

I am trying to set the directory to a default location
with the xlDialogSaveAs command but can't seem to get it
to work.

Here is my code:
MyDate = Replace(FormatDateTime(Now, vbShortDate), "/", "-
")
MyPath = "C:\Document and Settings\Administrator\My
Documents\Spreadsheets"
Application.Dialogs(xlDialogSaveAs).Show (MyPath
& "\NewSpreadsheet" & MyDate & ".xls")


I have tried changing the directory and setting the
lpstrInitialDir neither of which have worked. The save as
dialog box keeps reverting to my desktop.

Thanks,
Jonathan

 

[Tom Ogilvy]

MyDate = _
Replace(FormatDateTime(Now, vbShortDate), "/", "-")
MyPath = _
"C:\Document and Settings\Administrator\My Documents\Spreadsheets"
Chdrive MyPath
ChDir MyPath
Application.Dialogs(xlDialogSaveAs).Show _
(MyPath & "\NewSpreadsheet" & MyDate & ".xls")

Should work.