I have a workbook created in Excel 2002. Its name always includes the
current date, for example: "040409 Securities.xls". (I have always formatted
the date as YYMMDD, but would change that format if it assisted my doing
what I mention below.) The file is updated Monday through Friday afternoons
each week.

I have recently automated almost every step of the updating. Now, when I
open the most recent file, all updating is done automatically up to and
including the opening of the Save As dialog box. However, that box always
includes, in the default file name for saving, a date earlier than the
current date. I then have to change that date manually to today's date
before saving the file.

I found in an old MS KB article (139723) a method of prompting in the Save
As dialog box to save a file with a particular name:
Application.Dialogs(xlDialogSaveAs).Show ("xxx.xls"). That also worked for
Excel 2002, I found.

However, is there a way in which I can have that box prompt me to save the
file with the date part of the file name already being whatever is the
current date?

Hi

Application.Dialogs(xlDialogSaveAs).Show (Format(Date, "yymmdd") &
"xxx.xls")

HTH. Best wishes Harald

"Leslie" <leslieunderscorekatz@agddotnswdotgovdotau skrev i melding
...

Application.Dialogs(xlDialogSaveAs).Show ("xxx.xls"). That also worked for
Excel 2002, I found.

However, is there a way in which I can have that box prompt me to save the
file with the date part of the file name already being whatever is the
current date?

Hi
try something like the following:

Sub foo()
Dim old_fname
Dim new_fname
Dim save_fname
old_fname = "040409 Securities.xls"
new_fname = Format(Date, "MMDDYY") & Mid(old_fname, 7)
save_fname = Application.GetSaveAsFilename(new_fname)
MsgBox save_fname
'Me.SaveAs Filename:=save_fname

End Sub


--
Regards
Frank Kabel
Frankfurt, Germany


Leslie wrote:
I have a workbook created in Excel 2002. Its name always includes the
current date, for example: "040409 Securities.xls". (I have always
formatted the date as YYMMDD, but would change that format if it
assisted my doing what I mention below.) The file is updated Monday
through Friday afternoons each week.

I have recently automated almost every step of the updating. Now,
when I open the most recent file, all updating is done automatically
up to and including the opening of the Save As dialog box. However,
that box always includes, in the default file name for saving, a date
earlier than the current date. I then have to change that date
manually to today's date before saving the file.

I found in an old MS KB article (139723) a method of prompting in the
Save As dialog box to save a file with a particular name:
Application.Dialogs(xlDialogSaveAs).Show ("xxx.xls"). That also
worked for Excel 2002, I found.

However, is there a way in which I can have that box prompt me to
save the file with the date part of the file name already being
whatever is the current date?