Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 772
  • Last Modified:

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

0
antonioking
Asked:
antonioking
  • 6
  • 4
2 Solutions
 
zorvek (Kevin Jones)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
 
Arno KosterCommented:
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)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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now