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]

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

 

[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]