ActiveWorkbook.SaveAs saving as FALSE when selecting No

The following code saves the file as "FALSE.xls" if I select no when the dialog box pops up.

How can I stop it saving the file when I select no?
strFileLocation = "C:\"
strFileName = "Report.xls"
ActiveWorkbook.SaveAs Filename:=Application.GetSaveAsFilename(InitialFileName:=strFileLocation & strFileName)

Open in new window

antoniokingAsked:
Who is Participating?
 
Arno KosterConnect With a Mentor Commented:
Zorvek,

although the idea is correct, the getsaveasfilename returns a boolean false when cancelled, not the string "False".

thus :
strFileLocation = "C:\"
strFileName = "Report.xls"
objFilename=Application.GetSaveAsFilename(InitialFileName:=strFileLocation & strFileName)
if objFilename = false then
   msgbox "Dialog cancelled."
else
   ActiveWorkbook.SaveAs objFilename
end if

Open in new window

0
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
The GetSaveAsFilename returns a full path to the selected file or False if Cancel was clicked. If assigned to a string the False is converted to "False". If assigned to a variant the variant is changed to a Boolean data type and set to False. The easiest way to use the GetSaveAsFilename function is to assign it to a string and then test for "False" as illustrated with the sample code below.

   Dim FilePath As String
   FilePath = Application.GetSaveAsFilename("My Workbook", "Microsoft Excel Workbook (*.xls), *.xls")
   If FilePath = "False" Then
      MsgBox "Dialog cancelled."
   Else
      ' Do something with the file path
   End If

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
akoster,

You didn't read my comments. The GetSaveAsFileName returns a string if a file was selected, and a Boolean set to False if not. VBA converts the Boolean to the string "False" when it's a Boolean and assigned to a string. And that's what I test for. I prefer to work with specific variable types than Variants and Objects as there is less overhead and a decreased risk of failure downstream.

Kevin
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Arno KosterCommented:
Zorvek, you are right about the specific variable type.
The drawback is that the string variant of the boolean false will be different for different languages.

eg. in dutch it will be "Onwaar"
therefor when the code is used in non-english computers, it will have to be updated to match the current language. This might not be a problem in your case or for the author of the question, but it is for me.
0
 
zorvek (Kevin Jones)ConsultantCommented:
Excellent point!

My new code:

   Dim FilePath As Variant
   FilePath = Application.GetSaveAsFilename("My Workbook", "Microsoft Excel Workbook (*.xls), *.xls")
   If VarType(FilePath) = vbBoolean Then
      MsgBox "Dialog cancelled."
   Else
      ' Do something with the file path
   End If

Kevin
0
 
Arno KosterCommented:
or,

Dim FilePath As String
   FilePath = Application.GetSaveAsFilename("My Workbook", "Microsoft Excel Workbook (*.xls), *.xls")
   If FilePath = Cstr(False) Then
      MsgBox "Dialog cancelled."
   Else
      ActiveWorkbook.SaveAs FilePath
   End If
0
 
zorvek (Kevin Jones)ConsultantCommented:
That won't work because CStr always returns the U.S. spelling.

But this will:

   If CStr(FilePath) = "False" Then

Reference: http://www.oaltd.co.uk/excelprogref/ch22/ProgRefCh22.htm

Kevin
0
 
Arno KosterCommented:
Not at my workstation, I get the dutch string "Onwaar" as a result of CStr.
0
 
zorvek (Kevin Jones)ConsultantCommented:
I'm sooooo confused!

This seems vague: http://www.bettersolutions.com/vba/VTK243/NO215868332.htm

Agrees with me: http://msdn.microsoft.com/en-us/library/aa140178%28v=office.10%29.aspx

More vague but hints agreement with you: http://www.vb-helper.com/tip_internationalization.html

Agrees with me (same as above): http://www.oaltd.co.uk/excelprogref/Ch22/default.htm

Indicates agreement with you: http://msdn.microsoft.com/en-us/library/ch47ss2a%28v=vs.90%29.aspx

I'm thinking we are both right depending on the version. My guess is that it is WRS independent pre 2003 or 2007 and WRS dependent in later versions and VB.net

Time to go to the team!

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
antonioking:

Despite the apparent confusion, we can agree that akoster's first posted solution above works as does this regardless of Excel version and locale:

   Dim FilePath As Variant
   FilePath = Application.GetSaveAsFilename("My Workbook", "Microsoft Excel Workbook (*.xls), *.xls")
   If VarType(FilePath) = vbBoolean Then
      MsgBox "Dialog cancelled."
   Else
      ' Do something with the file path
   End If

Kevin
0
 
antoniokingAuthor Commented:
thanks guys
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.