Excel_VBA to prompt for me to type in filename 2

[MwTV]

Have the following code within a module that I am trying to modify so that I will be prompted to enter the filename of the Excel file.

ActiveWorkbook.SaveAs Filename:= _
"C:\Reports\Rpt_FranchiseDate______________.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


Every day, I am using MS Query to populate an Excel template with Oracle data. Upon bringing in the data, I use a command button at the top of the worksheet in Excel to set the column width, adjust the page setup, and save the file to a predefined path.

What I would like to do is have the code prompt me to enter the date at the end of the filename - like "Aug 22 2007" and have the file saved in the predefined path as
"Rpt_FranchiseDate_Aug 22 2007.xls." (Note, due to the date not always equal to the current date, I cannot automate the entry of the current day's date. It would be incorrect on Monday's due to the need to run the report to extract Saturday's and Sunday's data)

Also, would like the vba code to delete the command button on the worksheet and to remove all coding from the workbook that is related to the setting of the column width, page setup, and the saving to a defined path.

In other words, I do not want the recipients of the Report to see the command button or the code when they receive the Report via Outlook e-mail.

Is this possible?

Thanks in advance.

 

[SkipVought]

Please post VBA questions in Forum707.

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[wotgoesup]

Go into the VBA screen, and type "msgbox" into the search help.