Preventing FALSE.xls when automating SaveAs

**ljsmith** · 01-16-2006, 05:36 PM

For the type of work I do, I must lock down Excel spreadsheets for legal and risk reasons. We try to automate the spreadsheets as much as possible, and the main focus on current work is save and send functionality.

I have that working perfectly - including the generation of filename/subjectline through either specified information in a setInfo procedure or derived from information input by users.

However, when the user launches a save and send routine, if they cancel the save procedure (which is required for the send routine) it produces the FALSE.xls file.

Has anyone figured out how to prevent this?

Chip Pearson · 01-16-2006, 05:55 PM

I assume you're using GetSaveAsFileName. The variable that
receives the result of GetSaveAsFilename should be declare as
Variant not String.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"ljsmith" <[email protected]>
wrote in message
news:[email protected]...
>
> For the type of work I do, I must lock down Excel spreadsheets
> for legal
> and risk reasons. We try to automate the spreadsheets as much
> as
> possible, and the main focus on current work is save and send
> functionality.
>
> I have that working perfectly - including the generation of
> filename/subjectline through either specified information in a
> setInfo
> procedure or derived from information input by users.
>
> However, when the user launches a save and send routine, if
> they cancel
> the save procedure (which is required for the send routine) it
> produces
> the FALSE.xls file.
>
> Has anyone figured out how to prevent this?
>
>
> --
> ljsmith
> ------------------------------------------------------------------------
> ljsmith's Profile:
> http://www.excelforum.com/member.php...o&userid=30531
> View this thread:
> http://www.excelforum.com/showthread...hreadid=501828
>

**ljsmith** · 01-16-2006, 05:59 PM

That may be the ticket.

fileSaveName = Application.GetSaveAsFilename( _
fileName & ".xls", fileFilter:="Microsoft Excel Workbook (*.xls), *.xls")

is the code I'm using, both fileName and fileSaveName are listed as strings.

If I'm following what you're saying, it should be:

dim fileSaveName as Variant

rather than

dim fileSaveName as String

Correct?

Or should it be:

dim fileName as Variant

rather than

dim fileName as String

**ljsmith** · 01-16-2006, 06:05 PM

if it helps - here is the code for that subroutine.

Public Sub saveWkBook()
Dim fileSaveName As String

On Error GoTo ErrorHandler

Call setInfo

' this structures the filename that will be given to the saved file
If langChoice = "English" Then
fileName = "9868 - " & reqType & " " & myDate
ElseIf langChoice = "French" Then
fileName = "9868F - " & reqType & " " & myDate
End If

' this opens the Save As window and sets the filename and file type
fileSaveName = Application.GetSaveAsFilename( _
fileName & ".xls", fileFilter:="Microsoft Excel Workbook (*.xls), *.xls")

' this saves the workbook
ActiveWorkbook.SaveAs fileName:=fileSaveName, FileFormat:=xlNormal
' this generates the messagebox saying the file has been saved.
If langChoice = "English" Then
MsgBox "Saved as " & fileName
ElseIf langChoice = "French" Then
MsgBox "Enregistré sous " & fileName
End If
Exit Sub

ErrorHandler:
Exit Sub
End Sub

(Yeppers - I'm Canadian and deal with language switches too)

Dave Peterson · 01-16-2006, 06:10 PM

Dim FileSaveName as variant
filesavename = application.getsaveasfilename(....)

if filesavename = false then
'user hit cancel
exit sub
end if

'otherwise, keep going:
activeworkbook.saveas filename:=filesaveasname

ljsmith wrote:
>
> That may be the ticket.
>
> fileSaveName = Application.GetSaveAsFilename( _
> fileName & ".xls", fileFilter:="Microsoft Excel Workbook (*.xls),
> *.xls")
>
> is the code I'm using, both fileName and fileSaveName are listed as
> strings.
>
> If I'm following what you're saying, it should be:
>
> dim fileSaveName as Variant
>
> rather than
>
> dim fileSaveName as String
>
> Correct?
>
> Or should it be:
>
> dim fileName as Variant
>
> rather than
>
> dim fileName as String
>
> --
> ljsmith
>
> ------------------------------------------------------------------------
> ljsmith's Profile: http://www.excelforum.com/member.php...o&userid=30531
> View this thread: http://www.excelforum.com/showthread...hreadid=501828

--

Dave Peterson

Chip Pearson · 01-16-2006, 06:15 PM

That's correct, the variable should be a Variant. Then you can
check to see if it is False (not "False") and act accordingly.
E.g.,

fileSaveName = Application.GetSaveAsFilename( _
fileName & ".xls", fileFilter:="Microsoft Excel Workbook
(*.xls),*.xls")
If fileSaveName = False Then
' user cancelled
Else
msgbox fileSaveName
End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"ljsmith" <[email protected]>
wrote in message
news:[email protected]...
>
> That may be the ticket.
>
> fileSaveName = Application.GetSaveAsFilename( _
> fileName & ".xls", fileFilter:="Microsoft Excel Workbook
> (*.xls),
> *.xls")
>
> is the code I'm using, both fileName and fileSaveName are
> listed as
> strings.
>
> If I'm following what you're saying, it should be:
>
> dim fileSaveName as Variant
>
> rather than
>
> dim fileSaveName as String
>
> Correct?
>
> Or should it be:
>
> dim fileName as Variant
>
> rather than
>
> dim fileName as String
>
>
> --
> ljsmith
>
>
> ------------------------------------------------------------------------
> ljsmith's Profile:
> http://www.excelforum.com/member.php...o&userid=30531
> View this thread:
> http://www.excelforum.com/showthread...hreadid=501828
>

**ljsmith** · 01-16-2006, 06:21 PM

Originally Posted by Chip Pearson

That's correct, the variable should be a Variant. Then you can
check to see if it is False (not "False") and act accordingly.
E.g.,

fileSaveName = Application.GetSaveAsFilename( _
fileName & ".xls", fileFilter:="Microsoft Excel Workbook
(*.xls),*.xls")
If fileSaveName = False Then
' user cancelled
Else
msgbox fileSaveName
End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Thank you! That worked perfectly. You have no idea how long I've been trying to solve that problem.

**bvm** · 09-11-2007, 09:48 AM

Originally Posted by Chip Pearson

That's correct, the variable should be a Variant. Then you can
check to see if it is False (not "False") and act accordingly.
E.g.,

fileSaveName = Application.GetSaveAsFilename( _
fileName & ".xls", fileFilter:="Microsoft Excel Workbook
(*.xls),*.xls")
If fileSaveName = False Then
' user cancelled
Else
msgbox fileSaveName
End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"ljsmith" <[email protected]>
wrote in message
news:[email protected]...
>
> That may be the ticket.
>
> fileSaveName = Application.GetSaveAsFilename( _
> fileName & ".xls", fileFilter:="Microsoft Excel Workbook
> (*.xls),
> *.xls")
>
> is the code I'm using, both fileName and fileSaveName are
> listed as
> strings.
>
> If I'm following what you're saying, it should be:
>
> dim fileSaveName as Variant
>
> rather than
>
> dim fileSaveName as String
>
> Correct?
>
> Or should it be:
>
> dim fileName as Variant
>
> rather than
>
> dim fileName as String
>
>
> --
> ljsmith
>
>
> ------------------------------------------------------------------------
> ljsmith's Profile:
> http://www.excelforum.com/member.php...o&userid=30531
> View this thread:
> http://www.excelforum.com/showthread...hreadid=501828
>

Hi Chip,

I'm trying to setup a macro and then use it in a custom menu to automate File Save As option. I'm just a beginner in vba created couple of small automattion for printing etc. I need to automate SAVE AS option to give the filename a specific name based on couple of cell entries in the Sheet.

I am designing a simple Invoice spreadsheet for myself and want to name the workbook by 1) Where it is an Invoice (abbr: "I"), or Credit Note ("CN"), Quote ("Q") or Proforma Invoice ("PI"). This would be the Prefix character in the filename. I have setup a drop list (using Validation menu) with the above options which is in one cell and once selected it apprears on the screen.

2) In another cell I will enter invoice number, may be YYMM-001 for example.

I would like to create a vba code which will when I select save option give the spreadsheet a filename determind by options above e.g. for invoice it would name it as: I-200709-001.xls for Credit note: CN-200709-001.

Also I would like to specify a specfic path which will be used to save the file.

Can you help?
Alpen