Macro help- Prompt to save as, user to input name and destination

[jmthompson]

Good morning,
I am within millimeters of finishing a massive macro. I want to close my macro with code to generate a prompt upon completion of the macro to have the user "Save As" since I don't want them ruining my lovely template.

Since this template will be going to multiple users, each with different requirements for the new name and destination for the new file, I would like the macro to have the user enter the file name and for the default destination to be either wherever they've saved the template originally or for the user to enter that information.

I searched the board and found this code to prompt the user to enter the file name, but it lists a known destination for the file. Any ideas on how to doctor it to perform as I desire?

Dim strFileName As String
strFileName = InputBox(Prompt:="Please enter the file name.", Title:="Save As", Default:="Partner Program Report AXA")
"\\svrau100qsm01.com\Reports\Partner Reporting\Partner Flows\" + strFileName + ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


TIA!

 

[JadonR]

Dim file_name as variant    
file_name = Application.GetSaveAsFilename(FileFilter:="Microsoft Excel file (*.xls), *.xls")
    If file_name <> False Then
      ActiveWorkbook.SaveAs FileName:=file_name
      MsgBox "File Saved!"
    End If

Will do the trick.

Note you are defining file_name as a variant because if the user cancels the save as dialogue box it will return FALSE (and cause a type mismatch if you set it as a string)

Note if you want to stop users accidentally saving over the original save it as a template (XLT). When they open it it will automatically change the filename to have a 1 at the end & change it to xls. If they just hit 'save' they will the get the save as dialogue anyway

Can somebody modify this so it saves as a csv file instead?

 

[pycoh]

great work. simple and neat!

but how about if the sheet is protected with a password?