Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Workbook name change on update
Can anyone help me with this. I am not exactly sure what Dave Peterson means.
Q: Is the activeworkbook the same as the workbook that was opened? A: Yes, but when the Workbook is opened it has one file name, but as soon as you update it and save it the name changes. Then when you try and re-update it the new name is not found, so I get the run Time Error. Not exactly sure what you mean: If yes, then declare the bk variable in a General module and make it public. Public bk as workbook (Remove the dim statement in the open procedure.) Then use the bk variable to saveas bk.saveas And use bk as object to represent that workbook--no matter what the name is. What's happening is when I save the workbook to a different name, because it auto assigns the file name to according to the information on the User form. The Workbook changes from the name "Master Engineering Spec.xlsm" to the following. strFile = "SPEC " & CLLI_Code_1.Value _ & Space(1) & TEO_No_1.Value _ & Space(1) & CES_No_1.Value _ & Space(1) & TEO_Appx_No_2.Value I need help in changing the code so that it will recognize the new file name. I posted all the code in my prior post on 1/7/2010 "Run Time Error on File Name". If you need me to repost the code let me know. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Workbook name change on update
If you have not already tried this modified version, then try it and see if
you still get the Subscript out of Range message. Private Sub Save_Engineering_Spec_11_Click() Dim strFile As String Dim fileSaveName As Variant Dim myMsg As String strFile = "SPEC " & CLLI_Code_1.Value _ & Space(1) & TEO_No_1.Value _ & Space(1) & CES_No_1.Value _ & Space(1) & TEO_Appx_No_2.Value fileSaveName = Application.GetSaveAsFilename _ (InitialFileName:=strFile, _ fileFilter:="Excel Macro-Enabled Workbook(*.xlsm),(*.xlsm") If fileSaveName < False Then ActiveWorkbook.SaveAs Filename:= _ fileSaveName, _ FileFormat:=xlOpenXMLWorkbookMacroEnabled, _ CreateBackup:=False Workbooks.Open ("Master Engineering Spec.xlsm") Else MsgBox prompt:=Engineer_2.Value & vbLf & "You canceled saving the Engineering Spec." & vbCrLf & _ "Engineering Spec was not Saved.", _ Title:="C.E.S." End If End Sub I added a line of code to re-open the Master Engineering Spec file. That is the one that is causing the error when you try to correct an input error, because it goes away as soon as you execute the SaveAs. Try doing the corrective update process using this version and see if it still errors out. If it does you might have to unload the UserForm and and re-load it. With regards to Dave's suggestion about the Public variable, I do not believe he was aware that you are saving the form data in the Master Engineering Spec file. He was attempting to allow you to use the same variable for different workbooks as you open them. That would probably prevent the error message, but not cure the logic problem. You would open a workbook but not necessarily have access to your previous form data. "Brian" wrote in message ... Can anyone help me with this. I am not exactly sure what Dave Peterson means. Q: Is the activeworkbook the same as the workbook that was opened? A: Yes, but when the Workbook is opened it has one file name, but as soon as you update it and save it the name changes. Then when you try and re-update it the new name is not found, so I get the run Time Error. Not exactly sure what you mean: If yes, then declare the bk variable in a General module and make it public. Public bk as workbook (Remove the dim statement in the open procedure.) Then use the bk variable to saveas bk.saveas And use bk as object to represent that workbook--no matter what the name is. What's happening is when I save the workbook to a different name, because it auto assigns the file name to according to the information on the User form. The Workbook changes from the name "Master Engineering Spec.xlsm" to the following. strFile = "SPEC " & CLLI_Code_1.Value _ & Space(1) & TEO_No_1.Value _ & Space(1) & CES_No_1.Value _ & Space(1) & TEO_Appx_No_2.Value I need help in changing the code so that it will recognize the new file name. I posted all the code in my prior post on 1/7/2010 "Run Time Error on File Name". If you need me to repost the code let me know. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update Personal Workbook Macros when Workbook is opened. | Excel Programming | |||
workbook change and update | Excel Worksheet Functions | |||
Can one workbook/sheet update another workbook? | Excel Discussion (Misc queries) | |||
Change workbook sheet reference using cell A1 to change a vairable | Excel Worksheet Functions | |||
Update on change | Excel Programming |