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

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"
wrote in message
...

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

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

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

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"
wrote in message
...

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

Chip Pearson Wrote:
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.


--
ljsmith


------------------------------------------------------------------------
ljsmith's Profile: http://www.excelforum.com/member.php...o&userid=30531
View this thread: http://www.excelforum.com/showthread...hreadid=501828

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)


--
ljsmith


------------------------------------------------------------------------
ljsmith's Profile: http://www.excelforum.com/member.php...o&userid=30531
View this thread: http://www.excelforum.com/showthread...hreadid=501828