VBA for file save as
 

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

VBA for file save as
 

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



"SharonP." wrote in message
...
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

VBA for file save as
 

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?

"Chip Pearson" wrote:

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



"SharonP." wrote in message
...
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

VBA for file save as
 

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.



SharonP. wrote:

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?

"Chip Pearson" wrote:

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



"SharonP." wrote in message
...
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




--

Dave Peterson

VBA for file save as
 

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

"Dave Peterson" wrote:

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.



SharonP. wrote:

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?

"Chip Pearson" wrote:

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



"SharonP." wrote in message
...
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




--

Dave Peterson