Specify a default file name in VB when the Save / Save As command.

[Guest]

Hello,

I would like to specify a default file name when a user selects either the
Save or Save As commands from the Menu bar in excel.

I have thought about inserting a button on the excel spreadsheet and using
VB to load the Save / SaveAs dialog box, but this would mean disabling the
normal Save / Save As menu command buttons.

The default File name would include a static name XXX followed by todays date.

Any advise would be greatly apprieciated.

Many Thanks

SM

 

[Tom Ogilvy]

If you want to control the name assigned to the file, then you will need to
provide the interface with the user.

sName = Application.GetSaveAsFilename( InitialFilename:="XXX" &
format(date,"yyyymmdd") & ".xls")

sName will contain the fully qualified path the user had chosen, but
getsaveasfilename does not save the file. You can then save it with

Activeworkbook.SaveAs sName

But if you already know what the name should be, why involve the user. Why
not just save the file with that name.