Solved

ActiveWorkbook.SaveAs saving as FALSE when selecting No

Posted on 2011-03-16
11
733 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
  • 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:
akoster 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
 
LVL 19

Expert Comment

by:akoster
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 19

Expert Comment

by:akoster
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:akoster
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now