Solved

ActiveWorkbook.SaveAs saving as FALSE when selecting No

Posted on 2011-03-16
11
766 Views
Last Modified: 2012-05-11
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
Comment
Question by:antonioking
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 100 total points
ID: 35150994
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
 
LVL 19

Accepted Solution

by:
Arno Koster earned 400 total points
ID: 35158086
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35159048
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 19

Expert Comment

by:Arno Koster
ID: 35164058
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35164090
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
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35164223
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35164246
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
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35164589
Not at my workstation, I get the dutch string "Onwaar" as a result of CStr.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35167558
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35167589
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
 

Author Closing Comment

by:antonioking
ID: 35187953
thanks guys
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question