Solved

ActiveWorkbook.SaveAs saving as FALSE when selecting No

Posted on 2011-03-16
11
720 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

705 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

21 Experts available now in Live!

Get 1:1 Help Now