Application.GetSaveAsFilename HELP

[McTavish14]

Afternoon, I'm currently using the following (basic) code, but for some reason it doesnt actually save to any location. The actions all complete as expected, except for actually saving it.

Sub Xxxxx_SaveAs()
Application.GetSaveAsFilename

End Sub


The Save As window opens, the File name is populated as expected, the Save as type is All Files (*.*) and I am not sure if this is an issue (?).

Hit the Save button and you think all is done. However, you got to the save location and there is nothing.

Any help would be greatly appreciated.

I'm very much a novice when it comes to VBA!

 

[VoG]

Try like this

Sub Xxxxx_SaveAs()
Dim fname
fname = Application.GetSaveAsFilename
ActiveWorkbook.SaveAs Filename:=fname
End Sub

 

[McTavish14]

Try like this

Sub Xxxxx_SaveAs()
Dim fname
fname = Application.GetSaveAsFilename
ActiveWorkbook.SaveAs Filename:=fname
End Sub

This works a treat although it does error if you decide to Cancel your Save / Save As stating that A file named 'FALSE.xls' already exists in this location. Do you want to replace it? Selecting NO raises Run-time error '1004': Method 'SaveAs' of object '_Workbook' failed.

Further, is there a way to default the Save as type to .xls?

Thanks for the help.

 

[VoG]

Try this

Sub Xxxxx_SaveAs()
Dim fname
fname = Application.GetSaveAsFilename(InitialFileName:="", FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Save As")
If fname = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=fname
End Sub

 

[McTavish14]

Try this

Sub Xxxxx_SaveAs()
Dim fname
fname = Application.GetSaveAsFilename(InitialFileName:="", FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Save As")
If fname = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=fname
End Sub

Superb! Thank you very much.

I've populated the "" with the desired filename prefix and this now works great.

One last question if I may please - could any of this code fail as a result of compatibility? The majority of my users will currently be using 2003, but we are ungoing a move to 2010.

Thanks again.

 

[VoG]

That will work in Excel 2010 (just tested to be sure) but you might want to change .XLS to .XLSX or .XLSM if you want to save in an Excel 2010 file format. And you may need to specify the file type - see Use VBA SaveAs in Excel 2007-2010

 

[McTavish14]

Thank you - I will do. Your help has been much appreciated and has solved my issue. Superb!