software

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 quot;U:\ACTUARIAL\Renewalsquot;
sFilename = Application.GetSaveAsFilename(quot;quot;, quot;excel files (*.xls),*.xlsquot;)
ActiveWorkbook.SaveAs Filename:=sFilename

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

Dim Fame As Variant
FName = Application.GetSaveAsFilename(quot;quot;, quot;Excel Files
(*.xls),*.xlsquot;)
If FName = False Then
Debug.Print quot;user clicked cancelquot;
Else
Debug.Print quot;user chose quot; amp; FName
End If--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
quot;SharonP.quot; gt; wrote in message
...
gt; When I use the following code, the File/Save As Windows dialog
gt; box opens and
gt; as long as a file name is supplied - the file saves properly.
gt; However, if
gt; you click Cancel and don't provide a file name - a false.xls
gt; file is created.
gt; Is there a way to avoid this? What I want is just for the save
gt; as function
gt; to cancel without a save.
gt;
gt; 'Prompts to save the TLR file with the appropriate name
gt;
gt; ChDir quot;U:\ACTUARIAL\Renewalsquot;
gt; sFilename = Application.GetSaveAsFilename(quot;quot;, quot;excel files
gt; (*.xls),*.xlsquot;)
gt; ActiveWorkbook.SaveAs Filename:=sFilename
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?

quot;Chip Pearsonquot; wrote:

gt; Declare sFileName as a Variant, not a String, and test its value
gt; for False. E.g.,
gt;
gt; Dim Fame As Variant
gt; FName = Application.GetSaveAsFilename(quot;quot;, quot;Excel Files
gt; (*.xls),*.xlsquot;)
gt; If FName = False Then
gt; Debug.Print quot;user clicked cancelquot;
gt; Else
gt; Debug.Print quot;user chose quot; amp; FName
gt; End If
gt;
gt;
gt; --
gt; Cordially,
gt; Chip Pearson
gt; Microsoft MVP - Excel
gt; Pearson Software Consulting, LLC
gt; www.cpearson.com
gt;
gt;
gt;
gt; quot;SharonP.quot; gt; wrote in message
gt; ...
gt; gt; When I use the following code, the File/Save As Windows dialog
gt; gt; box opens and
gt; gt; as long as a file name is supplied - the file saves properly.
gt; gt; However, if
gt; gt; you click Cancel and don't provide a file name - a false.xls
gt; gt; file is created.
gt; gt; Is there a way to avoid this? What I want is just for the save
gt; gt; as function
gt; gt; to cancel without a save.
gt; gt;
gt; gt; 'Prompts to save the TLR file with the appropriate name
gt; gt;
gt; gt; ChDir quot;U:\ACTUARIAL\Renewalsquot;
gt; gt; sFilename = Application.GetSaveAsFilename(quot;quot;, quot;excel files
gt; gt; (*.xls),*.xlsquot;)
gt; gt; ActiveWorkbook.SaveAs Filename:=sFilename
gt;
gt;
gt;

Option Explict
Sub testme01()
Dim sFilename As Variant
sfilename = Application.GetSaveAsFilename(quot;quot;, quot;Excel Files (*.xls),*.xlsquot;)
If sfilename = False Then
'Debug.Print quot;user clicked cancelquot;
'do nothing
Else
'Debug.Print quot;user chose quot; amp; 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:
gt;
gt; I'm still struggling with this. I'm a VBA wantabe. I see how the if test
gt; works but then after the if test - how do you tell it not to write a
gt; false.xls?
gt;
gt; quot;Chip Pearsonquot; wrote:
gt;
gt; gt; Declare sFileName as a Variant, not a String, and test its value
gt; gt; for False. E.g.,
gt; gt;
gt; gt; Dim Fame As Variant
gt; gt; FName = Application.GetSaveAsFilename(quot;quot;, quot;Excel Files
gt; gt; (*.xls),*.xlsquot;)
gt; gt; If FName = False Then
gt; gt; Debug.Print quot;user clicked cancelquot;
gt; gt; Else
gt; gt; Debug.Print quot;user chose quot; amp; FName
gt; gt; End If
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Cordially,
gt; gt; Chip Pearson
gt; gt; Microsoft MVP - Excel
gt; gt; Pearson Software Consulting, LLC
gt; gt; www.cpearson.com
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;SharonP.quot; gt; wrote in message
gt; gt; ...
gt; gt; gt; When I use the following code, the File/Save As Windows dialog
gt; gt; gt; box opens and
gt; gt; gt; as long as a file name is supplied - the file saves properly.
gt; gt; gt; However, if
gt; gt; gt; you click Cancel and don't provide a file name - a false.xls
gt; gt; gt; file is created.
gt; gt; gt; Is there a way to avoid this? What I want is just for the save
gt; gt; gt; as function
gt; gt; gt; to cancel without a save.
gt; gt; gt;
gt; gt; gt; 'Prompts to save the TLR file with the appropriate name
gt; gt; gt;
gt; gt; gt; ChDir quot;U:\ACTUARIAL\Renewalsquot;
gt; gt; gt; sFilename = Application.GetSaveAsFilename(quot;quot;, quot;excel files
gt; gt; gt; (*.xls),*.xlsquot;)
gt; gt; gt; ActiveWorkbook.SaveAs Filename:=sFilename
gt; gt;
gt; gt;
gt; gt;

--

Dave Peterson

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

quot;Dave Petersonquot; wrote:

gt; Option Explict
gt; Sub testme01()
gt; Dim sFilename As Variant
gt; sfilename = Application.GetSaveAsFilename(quot;quot;, quot;Excel Files (*.xls),*.xlsquot;)
gt; If sfilename = False Then
gt; 'Debug.Print quot;user clicked cancelquot;
gt; 'do nothing
gt; Else
gt; 'Debug.Print quot;user chose quot; amp; FName
gt; ActiveWorkbook.SaveAs Filename:=sFilename
gt; End If
gt; End if
gt;
gt; You'd only do the saveas if the user didn't cancel the dialog. If they did hit
gt; the cancel button, you wouldn't do anything.
gt;
gt;
gt;
gt; SharonP. wrote:
gt; gt;
gt; gt; I'm still struggling with this. I'm a VBA wantabe. I see how the if test
gt; gt; works but then after the if test - how do you tell it not to write a
gt; gt; false.xls?
gt; gt;
gt; gt; quot;Chip Pearsonquot; wrote:
gt; gt;
gt; gt; gt; Declare sFileName as a Variant, not a String, and test its value
gt; gt; gt; for False. E.g.,
gt; gt; gt;
gt; gt; gt; Dim Fame As Variant
gt; gt; gt; FName = Application.GetSaveAsFilename(quot;quot;, quot;Excel Files
gt; gt; gt; (*.xls),*.xlsquot;)
gt; gt; gt; If FName = False Then
gt; gt; gt; Debug.Print quot;user clicked cancelquot;
gt; gt; gt; Else
gt; gt; gt; Debug.Print quot;user chose quot; amp; FName
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Cordially,
gt; gt; gt; Chip Pearson
gt; gt; gt; Microsoft MVP - Excel
gt; gt; gt; Pearson Software Consulting, LLC
gt; gt; gt; www.cpearson.com
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;SharonP.quot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; When I use the following code, the File/Save As Windows dialog
gt; gt; gt; gt; box opens and
gt; gt; gt; gt; as long as a file name is supplied - the file saves properly.
gt; gt; gt; gt; However, if
gt; gt; gt; gt; you click Cancel and don't provide a file name - a false.xls
gt; gt; gt; gt; file is created.
gt; gt; gt; gt; Is there a way to avoid this? What I want is just for the save
gt; gt; gt; gt; as function
gt; gt; gt; gt; to cancel without a save.
gt; gt; gt; gt;
gt; gt; gt; gt; 'Prompts to save the TLR file with the appropriate name
gt; gt; gt; gt;
gt; gt; gt; gt; ChDir quot;U:\ACTUARIAL\Renewalsquot;
gt; gt; gt; gt; sFilename = Application.GetSaveAsFilename(quot;quot;, quot;excel files
gt; gt; gt; gt; (*.xls),*.xlsquot;)
gt; gt; gt; gt; ActiveWorkbook.SaveAs Filename:=sFilename
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;