Workbook does not Save from User Form

[Brian]

I pasted this code in my User Form to assign the Name & Save a Workbook as
varibles from different Text Boxes. For some reason it assigns the name
correctly, but when you click on the save in the Save As Dialog box, only the
Error Message pops up and the Workbookbook does not save.

What am I missing?


'Save Engineering Spec 11 Control Button
Private Sub Save_Eng_Spec_11_Click()

Dim strFile As String
Dim bk As Workbook

strFile = "SPEC " & TEO_No_1.Value _
& Space(1) & CLLI_Code_1.Value _
& Space(1) & CES_No_1.Value _
& Space(1) & TEO_Appx_No_2.Value

strFile = Application.GetSaveAsFilename( _
InitialFileName:=strFile, _
fileFilter:="Excel Files (*.xls;*.xlsm;*.xlst), " & _
"*.xls;*.xlsm;*.xlst")

If FileToSave = False Then

MsgBox "The Save Method Failed, Eng Spec was not Saved", , "C.E.S."

Exit Sub

End If

End Sub

 

[OssieMac]

Hi Brian,

I cannot see anywhere in your code where you have assigned anything to the
variable FileToSave.

Therefore it will always be false if it has not been declared or has been
declared as a variant. If declared as anything else other than variant or
boolean then I believe it will return an error.

The following example tests whether or not a workbook has unsaved changes.

If Workbooks("Book1.xlsm").Saved Then
MsgBox "Workbook has NO unsaved changes"
Else
MsgBox "Workbook contains unsaved changes"
End If

You can assign the workbook name to a variable and use it in lieu of the
actual name as per the following.

FileToSave = "Book1.xlsm"

If Workbooks(FileToSave).Saved Then

You can also use ThisWorkbook as per the following

If ThisWorkbook.Saved Then

 

[OssieMac]

For the Save As I suggest that you record the code and then replace the
filename with your string variable.

 

[joel]

Oops!!! FileToSave is the variable used in the VBAhelp menu for th
function GetSaveFileName. You don't have it sedt to anything in you
code so it is equal to nothing which is equivalent to FALSE. You ar
using strFile twice in the code for two different puposes. Als
GetSaveAsFilename doesn't save a file, it just gets the name. You hav
to do the Save. Make the changes below


From

strFile = "SPEC " & TEO_No_1.Value _
& Space(1) & CLLI_Code_1.Value _
& Space(1) & CES_No_1.Value _
& Space(1) & TEO_Appx_No_2.Value

strFile = Application.GetSaveAsFilename( _ <======= This line i
wrong
InitialFileName:=strFile, _
fileFilter:="Excel Files (*.xls;*.xlsm;*.xlst), " & _
"*.xls;*.xlsm;*.xlst")

If FileToSave = False Then <======= Should match Variable here

MsgBox "The Save Method Failed, Eng Spec was not Saved", , "C.E.S."

<============================= Add

Exit Sub




To :

strFile = "SPEC " & TEO_No_1.Value _
& Space(1) & CLLI_Code_1.Value _
& Space(1) & CES_No_1.Value _
& Space(1) & TEO_Appx_No_2.Value

FileToSave = Application.GetSaveAsFilename( _ <======= This line i
wrong
InitialFileName:=strFile, _
fileFilter:="Excel Files (*.xls;*.xlsm;*.xlst), " & _
"*.xls;*.xlsm;*.xlst")

If FileToSave = False Then <======= Should match Variable here

MsgBox "The Save Method Failed, Eng Spec was not Saved", , "C.E.S."

Else <============================= Add
Thisworkbook.saveas FileName:=FileToSave
Exit Su

 

[OssieMac]

Hi again Brian,

I was a bit confused by what you were attempting to do but now I think that
I might understand your problem.

GetSaveAsFilename Displays the standard Save As dialog box and gets a file
name from the user WITHOUT ACTUALLY SAVING ANY FILES.

If the user does not select a filename or cancels etc then the filename will
return as false. The user can also edit the file name in the dialog box.
Clicking on Save in the dialog box only gets a filename. It does not save as
one would expect.

You only use this if you want the user to select an existing file or one
that follows a particular pattern like the following example and perhaps then
edit the name.

Note that fileSaveName must be declared as a Variant so that it can return a
boolean value of false or the filename string otherwise the code will error.

The fileFilter must follow the names that you see in the save as dialog box
under normal use of Save As. I have use a macro enabled which is only
available in xl2007.

Of course you can edit the code to create strFile whatever you want. I did
not have the values that you have used.

Unless you want the user to be able to select the filename then there is
really no point in using GetSaveAsFilename. Go straight to
ActiveWorkbook.SaveAs Filename:= .

The Excel 2007 help is misleading because it only places a message where the
SaveAs need to be.

Sub UsingGetSaveAsFilename()

Dim fileSaveName As Variant
Dim strFile As String

strFile = "This New*"

fileSaveName = Application.GetSaveAsFilename _
(InitialFileName:=strFile, _
fileFilter:="Excel Macro-Enabled Workbook(*.xlsm),(*.xlsm")

If fileSaveName <> False Then
ActiveWorkbook.SaveAs Filename:= _
fileSaveName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
CreateBackup:=False
Else
MsgBox "Not saved." & vbCrLf & _
"User cancelled without selecting a filename."

End If

End Sub

 

[Brian]

It worked, I had to make a few few small tweeks to it, but it works good.

 

[Chip Pearson]

Try code like the following. Change the value of FName to the inital
filename.

Sub DoSaveAs()

Dim FName As Variant
Dim Ext As String
Dim N As Long
Dim FileFormat As XlFileFormat
Dim Ndx As Long
FName = ThisWorkbook.FullName
N = InStrRev(FName, ".")
Ext = Mid(FName, N + 1)
Select Case LCase(Ext)
Case "xls": Ndx = 1
Case "xlsm": Ndx = 2
Case "xlsx": Ndx = 3
Case "xlsb": Ndx = 4
End Select

FName = Application.GetSaveAsFilename(FName, _
"Excel Files (*.xls),*.xls," & _
"Excel Files (*.xlsm),*.xlsm," & _
"Excel Files (*.xlsx),*.xlsx," & _
"Excel Files (*.xlsb),*.xlsb", _
Ndx)

If FName = False Then
' user cancelled. get out.
Exit Sub
End If
N = InStrRev(FName, ".")
Ext = Mid(FName, N + 1)
Select Case LCase(Ext)
Case "xls": FileFormat = xlExcel8
Case "xlsx": FileFormat = xlWorkbookDefault
Case "xlsb": FileFormat = xlExcel12
Case "xlsm": FileFormat = xlOpenXMLWorkbookMacroEnabled
End Select
On Error Resume Next
If StrComp(FName, ThisWorkbook.FullName, vbTextCompare) <> 0 Then
Kill FName
End If
On Error GoTo 0
ThisWorkbook.SaveAs Filename:=FName, FileFormat:=FileFormat

End Sub




Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]