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!

 

[Norie]

Don't doctor it.

Use Application.GetSaveAsFileName instead.

 

[jmthompson]

[quote/]Use Application.GetSaveAsFileName instead.[/quote]

Are you saying it is a simple as just including that one line in my macro?

Wow, I just tested it and it works exactly as promised. You rock!

 

[Norie]

Well I'm not quite sure it's as simple as just one line.

How have used the method in your code?

There are two things to note.

1 You need to check that the user hasn't pressed Cancel.

2 You actually need further code to save the file - the method only does what it says, gets a filename.

 

[jmthompson]

I seemed odd that only one line would accomplish my needs, but I wasn't about to look a gift horse in the mouth!

For now, I have just the line of code you suggested:
Application.GetSaveAsFilename

I am not too concerned about users hitting cancel, these folks are pretty compliant as long as I spell out the instructions for them. I really just wanted a reminder for them not to just hit save and overwrite the template. What code would I use in order to execute the save once they've selected the new file name and destination?

 

[random_noise]

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

 

[jmthompson]

Saving as a template gounds like a good solution. I have never worked with Excel templates before, do they act the same way as xls files?

 

[random_noise]

Yep, other than the behavior I described (i.e. prompting you for save name when first saving) once its saved as an xls it then just work same as any other excel file.

One thing to note is that when you select the file type of xlt Excel will change the directory its going to save in to your default template directory. Don't worry though you can still browse to any other directory to save it in.

 

[airborne4905]

The code worked great and does just about everything that I need it too. But can you give it a default directory to open the save as dialog box in?

 

[Norie]

Short answer is no - try using ChDir before Application.GetOpenFileName.