VBA for file save as

[Guest]

When I use the following code, the File/Save As Windows dialog box opens and
as long as a file name is supplied - the file saves properly. However, if
you click Cancel and don't provide a file name - a false.xls file is created.
Is there a way to avoid this? What I want is just for the save as function
to cancel without a save.

'Prompts to save the TLR file with the appropriate name

ChDir "U:\ACTUARIAL\Renewals"
sFilename = Application.GetSaveAsFilename("", "excel files (*.xls),*.xls")
ActiveWorkbook.SaveAs Filename:=sFilename

 

[Chip Pearson]

Declare sFileName as a Variant, not a String, and test its value
for False. E.g.,

Dim Fame As Variant
FName = Application.GetSaveAsFilename("", "Excel Files
(*.xls),*.xls")
If FName = False Then
Debug.Print "user clicked cancel"
Else
Debug.Print "user chose " & FName
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

I'm still struggling with this. I'm a VBA wantabe. I see how the if test
works but then after the if test - how do you tell it not to write a
false.xls?

 

[Dave Peterson]

Option Explict
Sub testme01()
Dim sFilename As Variant
sfilename = Application.GetSaveAsFilename("", "Excel Files (*.xls),*.xls")
If sfilename = False Then
'Debug.Print "user clicked cancel"
'do nothing
Else
'Debug.Print "user chose " & FName
ActiveWorkbook.SaveAs Filename:=sFilename
End If
End if

You'd only do the saveas if the user didn't cancel the dialog. If they did hit
the cancel button, you wouldn't do anything.

 

[Guest]

Thank both of you for the assistance with this. I understand the code and
it's working great for me.