Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook does not Save from User Form
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook does not Save from User Form
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 -- Regards, OssieMac "Brian" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook does not Save from User Form
For the Save As I suggest that you record the code and then replace the
filename with your string variable. -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook does not Save from User Form
Oops!!! FileToSave is the variable used in the VBAhelp menu for the function GetSaveFileName. You don't have it sedt to anything in your code so it is equal to nothing which is equivalent to FALSE. You are using strFile twice in the code for two different puposes. Also GetSaveAsFilename doesn't save a file, it just gets the name. You have 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 is 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 is 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 Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166274 Microsoft Office Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook does not Save from User Form
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 -- Regards, OssieMac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook does not Save from User Form
It worked, I had to make a few few small tweeks to it, but it works good.
"OssieMac" wrote: 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 -- Regards, OssieMac |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook does not Save from User Form
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] On Thu, 31 Dec 2009 20:25:01 -0800, Brian wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening Workbook from User Form | Excel Programming | |||
Date field in user form & Loading a user form on opening workbook | Excel Programming | |||
Using Button on User Form to save Data to specific Cell?? | Excel Programming | |||
VBA Question - Canceling a 'Save As' command from a user form | Excel Programming |