Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error on file name
I have this code when I go to update a new workbook, but once the workbook is
updated and saved, it will not allow you to reupdate it. I think the reason is because it's looking for the workbook "Master Engineering Spec.xlsm" and that is not the workbook name. Here is the sequence I am following. 1: Open User Form 2: Open New Engineering Spec (Control Button "Open_New_Engineer_Spec_8") 3: Fill in the user Form 4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10" 5: Save Engineering Spec workbook (I automaticaly assigns the name. Now here is where the problem is. You forgot to fill in something on the User Form. So you go back and put it in, then you click on the Control Button "Update_Engineer_Spec_10" and you get a run Time Error message. How can I get around this so that it updates the workbook? Do I need to add another Control Button to up date exsisting worksbooks? 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") 'Job Address Information .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value .Range("B12").Value = Me("State_1").Value .Range("C12").Value = Me("Zip_Code_1").Value More Code Here, but same as above. End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error on file name
This line doesn't look right.
..Range("A09").Value = Me("Office_1").Value Maybe you meant something like: ..Range("A09").Value = Me.controls("Office_1").Value =========== So somewhere you have a line that looks something like: With Workbooks("Master Engineering Spec.xlsm") .saveas filename:=..... .... Instead of refering to the workbook by its name, you can use a variable. Maybe even assign the variable when you open the workbook. dim wkbk as workbook .... set wkbk = workbooks.open(filename:="c:\somepath\Master Engineering Spec.xlsm") Then when you save it... wkbk.saveas filename:="C:\somenewname" But keep refering to that variable... with wkbk.workSheets("COVER SHEET") .Range("A09").Value = Me.controls("Office_1").Value Brian wrote: I have this code when I go to update a new workbook, but once the workbook is updated and saved, it will not allow you to reupdate it. I think the reason is because it's looking for the workbook "Master Engineering Spec.xlsm" and that is not the workbook name. Here is the sequence I am following. 1: Open User Form 2: Open New Engineering Spec (Control Button "Open_New_Engineer_Spec_8") 3: Fill in the user Form 4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10" 5: Save Engineering Spec workbook (I automaticaly assigns the name. Now here is where the problem is. You forgot to fill in something on the User Form. So you go back and put it in, then you click on the Control Button "Update_Engineer_Spec_10" and you get a run Time Error message. How can I get around this so that it updates the workbook? Do I need to add another Control Button to up date exsisting worksbooks? 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") 'Job Address Information .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value .Range("B12").Value = Me("State_1").Value .Range("C12").Value = Me("Zip_Code_1").Value More Code Here, but same as above. End With -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error on file name
Brian, I thought that was one of the functions of the button to repopulate
the form, to allow for updates to previously entered data. Is this the part that is not working? I read Dave's answer about the different workbook name and that would present a problem if you are trying to recall the data from that workbook. But I had the impression that you were storing the data to recover in the master workbook and would then resave the updates in the job workbook. Maybe my concept was all screwed up. "Brian" wrote in message ... I have this code when I go to update a new workbook, but once the workbook is updated and saved, it will not allow you to reupdate it. I think the reason is because it's looking for the workbook "Master Engineering Spec.xlsm" and that is not the workbook name. Here is the sequence I am following. 1: Open User Form 2: Open New Engineering Spec (Control Button "Open_New_Engineer_Spec_8") 3: Fill in the user Form 4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10" 5: Save Engineering Spec workbook (I automaticaly assigns the name. Now here is where the problem is. You forgot to fill in something on the User Form. So you go back and put it in, then you click on the Control Button "Update_Engineer_Spec_10" and you get a run Time Error message. How can I get around this so that it updates the workbook? Do I need to add another Control Button to up date exsisting worksbooks? 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") 'Job Address Information .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value .Range("B12").Value = Me("State_1").Value .Range("C12").Value = Me("Zip_Code_1").Value More Code Here, but same as above. End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error on file name
Everything works exactly as it should. When you open an exsisiting Work Book
it fills in the User Form from the Hidden Sheet in the Work Book, but when you change or add anything to the User Form and click on Update again it gives you a run Time Error 9, Subscript out of range. 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") ERROR The only thing I can think of is that the file name is not ("Master Engineering Spec.xlsm"). Remember the file name is assigned as follows: 'Save Engineering Spec 11 Control Button 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 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 was wondering if adding another set of update buttons work fix this problem. There will be a set for New Work Books and a set for Exsisiting Work Books. The New Work Book will Look for the Name "Master Engineering Spec.xlsm". The Exsisitng Work Book will Look for the Name 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 Either that or change the New Work Book to the name as the same varible as a Exsisiting Work Book. That way no matter Which Work Book is open it will see the "Spec" in the name and update it. "JLGWhiz" wrote: Brian, I thought that was one of the functions of the button to repopulate the form, to allow for updates to previously entered data. Is this the part that is not working? I read Dave's answer about the different workbook name and that would present a problem if you are trying to recall the data from that workbook. But I had the impression that you were storing the data to recover in the master workbook and would then resave the updates in the job workbook. Maybe my concept was all screwed up. "Brian" wrote in message ... I have this code when I go to update a new workbook, but once the workbook is updated and saved, it will not allow you to reupdate it. I think the reason is because it's looking for the workbook "Master Engineering Spec.xlsm" and that is not the workbook name. Here is the sequence I am following. 1: Open User Form 2: Open New Engineering Spec (Control Button "Open_New_Engineer_Spec_8") 3: Fill in the user Form 4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10" 5: Save Engineering Spec workbook (I automaticaly assigns the name. Now here is where the problem is. You forgot to fill in something on the User Form. So you go back and put it in, then you click on the Control Button "Update_Engineer_Spec_10" and you get a run Time Error message. How can I get around this so that it updates the workbook? Do I need to add another Control Button to up date exsisting worksbooks? 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") 'Job Address Information .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value .Range("B12").Value = Me("State_1").Value .Range("C12").Value = Me("Zip_Code_1").Value More Code Here, but same as above. End With . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error on file name
You are right about the Subscript out of Range message. It says the name
you are using cannot be found. Some reasons, the name does not exist, the search is directed to the wrong place or the file with that name is not open You stated that the problem occurred when you forgot to include something during an update and you want to fix it after the workbook has been updated and saved. 1. You previously stated that you would save the update info to the workbook running the code. That should provide access to data that will repopulate the UserForm when you click the update button. 2. To fix the workbook that was saved with missing info, it will need to be opened again. 3. You should be able to then modify the UserForm data and resave the workbook with corrections made. Where did I miss it? "Brian" wrote in message ... Everything works exactly as it should. When you open an exsisiting Work Book it fills in the User Form from the Hidden Sheet in the Work Book, but when you change or add anything to the User Form and click on Update again it gives you a run Time Error 9, Subscript out of range. 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") ERROR The only thing I can think of is that the file name is not ("Master Engineering Spec.xlsm"). Remember the file name is assigned as follows: 'Save Engineering Spec 11 Control Button 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 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 was wondering if adding another set of update buttons work fix this problem. There will be a set for New Work Books and a set for Exsisiting Work Books. The New Work Book will Look for the Name "Master Engineering Spec.xlsm". The Exsisitng Work Book will Look for the Name 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 Either that or change the New Work Book to the name as the same varible as a Exsisiting Work Book. That way no matter Which Work Book is open it will see the "Spec" in the name and update it. "JLGWhiz" wrote: Brian, I thought that was one of the functions of the button to repopulate the form, to allow for updates to previously entered data. Is this the part that is not working? I read Dave's answer about the different workbook name and that would present a problem if you are trying to recall the data from that workbook. But I had the impression that you were storing the data to recover in the master workbook and would then resave the updates in the job workbook. Maybe my concept was all screwed up. "Brian" wrote in message ... I have this code when I go to update a new workbook, but once the workbook is updated and saved, it will not allow you to reupdate it. I think the reason is because it's looking for the workbook "Master Engineering Spec.xlsm" and that is not the workbook name. Here is the sequence I am following. 1: Open User Form 2: Open New Engineering Spec (Control Button "Open_New_Engineer_Spec_8") 3: Fill in the user Form 4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10" 5: Save Engineering Spec workbook (I automaticaly assigns the name. Now here is where the problem is. You forgot to fill in something on the User Form. So you go back and put it in, then you click on the Control Button "Update_Engineer_Spec_10" and you get a run Time Error message. How can I get around this so that it updates the workbook? Do I need to add another Control Button to up date exsisting worksbooks? 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") 'Job Address Information .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value .Range("B12").Value = Me("State_1").Value .Range("C12").Value = Me("Zip_Code_1").Value More Code Here, but same as above. End With . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error on file name
BTW, as Dave pointed out, when you run the update and do a SaveAs operation,
you effectively close the parent workbook, so the cure for the problem could be as simple as adding a line of code after the SaveAs command to re-open the "Master Engineering Spec.xlsm" workbook. Check that out and see if that will work. "Brian" wrote in message ... Everything works exactly as it should. When you open an exsisiting Work Book it fills in the User Form from the Hidden Sheet in the Work Book, but when you change or add anything to the User Form and click on Update again it gives you a run Time Error 9, Subscript out of range. 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") ERROR The only thing I can think of is that the file name is not ("Master Engineering Spec.xlsm"). Remember the file name is assigned as follows: 'Save Engineering Spec 11 Control Button 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 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 was wondering if adding another set of update buttons work fix this problem. There will be a set for New Work Books and a set for Exsisiting Work Books. The New Work Book will Look for the Name "Master Engineering Spec.xlsm". The Exsisitng Work Book will Look for the Name 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 Either that or change the New Work Book to the name as the same varible as a Exsisiting Work Book. That way no matter Which Work Book is open it will see the "Spec" in the name and update it. "JLGWhiz" wrote: Brian, I thought that was one of the functions of the button to repopulate the form, to allow for updates to previously entered data. Is this the part that is not working? I read Dave's answer about the different workbook name and that would present a problem if you are trying to recall the data from that workbook. But I had the impression that you were storing the data to recover in the master workbook and would then resave the updates in the job workbook. Maybe my concept was all screwed up. "Brian" wrote in message ... I have this code when I go to update a new workbook, but once the workbook is updated and saved, it will not allow you to reupdate it. I think the reason is because it's looking for the workbook "Master Engineering Spec.xlsm" and that is not the workbook name. Here is the sequence I am following. 1: Open User Form 2: Open New Engineering Spec (Control Button "Open_New_Engineer_Spec_8") 3: Fill in the user Form 4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10" 5: Save Engineering Spec workbook (I automaticaly assigns the name. Now here is where the problem is. You forgot to fill in something on the User Form. So you go back and put it in, then you click on the Control Button "Update_Engineer_Spec_10" and you get a run Time Error message. How can I get around this so that it updates the workbook? Do I need to add another Control Button to up date exsisting worksbooks? 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") 'Job Address Information .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value .Range("B12").Value = Me("State_1").Value .Range("C12").Value = Me("Zip_Code_1").Value More Code Here, but same as above. End With . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error on file name
Would changing the file name on the New Workbook "Master Engineering
Spec.xlsm" to 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 Solve the Problem or would it be better to add another control button to update the StrFile? "JLGWhiz" wrote: BTW, as Dave pointed out, when you run the update and do a SaveAs operation, you effectively close the parent workbook, so the cure for the problem could be as simple as adding a line of code after the SaveAs command to re-open the "Master Engineering Spec.xlsm" workbook. Check that out and see if that will work. "Brian" wrote in message ... Everything works exactly as it should. When you open an exsisiting Work Book it fills in the User Form from the Hidden Sheet in the Work Book, but when you change or add anything to the User Form and click on Update again it gives you a run Time Error 9, Subscript out of range. 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") ERROR The only thing I can think of is that the file name is not ("Master Engineering Spec.xlsm"). Remember the file name is assigned as follows: 'Save Engineering Spec 11 Control Button 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 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 was wondering if adding another set of update buttons work fix this problem. There will be a set for New Work Books and a set for Exsisiting Work Books. The New Work Book will Look for the Name "Master Engineering Spec.xlsm". The Exsisitng Work Book will Look for the Name 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 Either that or change the New Work Book to the name as the same varible as a Exsisiting Work Book. That way no matter Which Work Book is open it will see the "Spec" in the name and update it. "JLGWhiz" wrote: Brian, I thought that was one of the functions of the button to repopulate the form, to allow for updates to previously entered data. Is this the part that is not working? I read Dave's answer about the different workbook name and that would present a problem if you are trying to recall the data from that workbook. But I had the impression that you were storing the data to recover in the master workbook and would then resave the updates in the job workbook. Maybe my concept was all screwed up. "Brian" wrote in message ... I have this code when I go to update a new workbook, but once the workbook is updated and saved, it will not allow you to reupdate it. I think the reason is because it's looking for the workbook "Master Engineering Spec.xlsm" and that is not the workbook name. Here is the sequence I am following. 1: Open User Form 2: Open New Engineering Spec (Control Button "Open_New_Engineer_Spec_8") 3: Fill in the user Form 4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10" 5: Save Engineering Spec workbook (I automaticaly assigns the name. Now here is where the problem is. You forgot to fill in something on the User Form. So you go back and put it in, then you click on the Control Button "Update_Engineer_Spec_10" and you get a run Time Error message. How can I get around this so that it updates the workbook? Do I need to add another Control Button to up date exsisting worksbooks? 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") 'Job Address Information .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value .Range("B12").Value = Me("State_1").Value .Range("C12").Value = Me("Zip_Code_1").Value More Code Here, but same as above. End With . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error on file name
Dave,
What I had to do was add a Hidden Sheet in the Work Book being saved in order to save all the Data in the User Form. Then when that Work Book is opened again it loads the Data back to the User Form from the Hidden Sheet. If you tell me what code you want to see I will post it. "Dave Peterson" wrote: This line doesn't look right. ..Range("A09").Value = Me("Office_1").Value Maybe you meant something like: ..Range("A09").Value = Me.controls("Office_1").Value =========== So somewhere you have a line that looks something like: With Workbooks("Master Engineering Spec.xlsm") .saveas filename:=..... .... Instead of refering to the workbook by its name, you can use a variable. Maybe even assign the variable when you open the workbook. dim wkbk as workbook .... set wkbk = workbooks.open(filename:="c:\somepath\Master Engineering Spec.xlsm") Then when you save it... wkbk.saveas filename:="C:\somenewname" But keep refering to that variable... with wkbk.workSheets("COVER SHEET") .Range("A09").Value = Me.controls("Office_1").Value Brian wrote: I have this code when I go to update a new workbook, but once the workbook is updated and saved, it will not allow you to reupdate it. I think the reason is because it's looking for the workbook "Master Engineering Spec.xlsm" and that is not the workbook name. Here is the sequence I am following. 1: Open User Form 2: Open New Engineering Spec (Control Button "Open_New_Engineer_Spec_8") 3: Fill in the user Form 4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10" 5: Save Engineering Spec workbook (I automaticaly assigns the name. Now here is where the problem is. You forgot to fill in something on the User Form. So you go back and put it in, then you click on the Control Button "Update_Engineer_Spec_10" and you get a run Time Error message. How can I get around this so that it updates the workbook? Do I need to add another Control Button to up date exsisting worksbooks? 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") 'Job Address Information .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value .Range("B12").Value = Me("State_1").Value .Range("C12").Value = Me("Zip_Code_1").Value More Code Here, but same as above. End With -- Dave Peterson . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error on file name
Here is the code to open a new workbook.
' Open New Engineer Spec 8 Control Button Private Sub Open_New_Engineer_Spec_8_Click() Dim myMsg As String On Error Resume Next Workbooks.Open ("Master Engineering Spec.xlsm") If Err.Number < 0 Then MsgBox prompt:=Engineer_2.Value & vbLf & "Your Open Method Failed, No Engineering Spec was Opened", _ Title:="C.E.Singleton Co. of Florida, Inc." ' MsgBox "The Open Method Failed, Engineering Spec was not Opened", , "C.E. Singleton Co. of Florida, Inc." End If End Sub "Dave Peterson" wrote: This line doesn't look right. ..Range("A09").Value = Me("Office_1").Value Maybe you meant something like: ..Range("A09").Value = Me.controls("Office_1").Value =========== So somewhere you have a line that looks something like: With Workbooks("Master Engineering Spec.xlsm") .saveas filename:=..... .... Instead of refering to the workbook by its name, you can use a variable. Maybe even assign the variable when you open the workbook. dim wkbk as workbook .... set wkbk = workbooks.open(filename:="c:\somepath\Master Engineering Spec.xlsm") Then when you save it... wkbk.saveas filename:="C:\somenewname" But keep refering to that variable... with wkbk.workSheets("COVER SHEET") .Range("A09").Value = Me.controls("Office_1").Value Brian wrote: I have this code when I go to update a new workbook, but once the workbook is updated and saved, it will not allow you to reupdate it. I think the reason is because it's looking for the workbook "Master Engineering Spec.xlsm" and that is not the workbook name. Here is the sequence I am following. 1: Open User Form 2: Open New Engineering Spec (Control Button "Open_New_Engineer_Spec_8") 3: Fill in the user Form 4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10" 5: Save Engineering Spec workbook (I automaticaly assigns the name. Now here is where the problem is. You forgot to fill in something on the User Form. So you go back and put it in, then you click on the Control Button "Update_Engineer_Spec_10" and you get a run Time Error message. How can I get around this so that it updates the workbook? Do I need to add another Control Button to up date exsisting worksbooks? 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") 'Job Address Information .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value .Range("B12").Value = Me("State_1").Value .Range("C12").Value = Me("Zip_Code_1").Value More Code Here, but same as above. End With -- Dave Peterson . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error on file name
Here is the code to open an exsisitng Workbook
' Open Existing Engineering Spec 9 Control Button Private Sub Open_Existing_Engineer_Spec_9_Click() Dim FileToOpen As Variant Dim bk As Workbook Dim LastBackSlashPos As Long Dim myMsg As String FileToOpen = Application.GetOpenFilename("SPEC (*.xlsm), Spec*.xlsm") If FileToOpen = False Then MsgBox prompt:=Engineer_2.Value & vbLf & "You canceled opening an Engineering Spec", _ Title:="C.E.Singleton Co. of Florida, Inc." Exit Sub End If LastBackSlashPos = InStrRev(FileToOpen, "\", -1, vbTextCompare) If UCase(Mid(FileToOpen, LastBackSlashPos + 1, 4)) < UCase("SPEC") Then MsgBox prompt:=Engineer_2.Value & vbLf & "You can only open an exsisting Engineering Spec", _ Title:="C.E.Singleton Co. of Florida, Inc." Exit Sub End If Set bk = Workbooks.Open(Filename:=FileToOpen) '========================================= With bk.Sheets("Job Data") ' Site Information: Me("CLLI_Code_1").Value = .Range("D02").Value Me("Office_1").Value = .Range("D03").Value Me("Address_11").Value = .Range("D04").Value Me("Address_12").Value = .Range("D05").Value Me("City_1").Value = .Range("D06").Value Me("State_1").Value = .Range("D07").Value Alot more code here the same as above '======================================== End With End Sub "Dave Peterson" wrote: This line doesn't look right. ..Range("A09").Value = Me("Office_1").Value Maybe you meant something like: ..Range("A09").Value = Me.controls("Office_1").Value =========== So somewhere you have a line that looks something like: With Workbooks("Master Engineering Spec.xlsm") .saveas filename:=..... .... Instead of refering to the workbook by its name, you can use a variable. Maybe even assign the variable when you open the workbook. dim wkbk as workbook .... set wkbk = workbooks.open(filename:="c:\somepath\Master Engineering Spec.xlsm") Then when you save it... wkbk.saveas filename:="C:\somenewname" But keep refering to that variable... with wkbk.workSheets("COVER SHEET") .Range("A09").Value = Me.controls("Office_1").Value Brian wrote: I have this code when I go to update a new workbook, but once the workbook is updated and saved, it will not allow you to reupdate it. I think the reason is because it's looking for the workbook "Master Engineering Spec.xlsm" and that is not the workbook name. Here is the sequence I am following. 1: Open User Form 2: Open New Engineering Spec (Control Button "Open_New_Engineer_Spec_8") 3: Fill in the user Form 4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10" 5: Save Engineering Spec workbook (I automaticaly assigns the name. Now here is where the problem is. You forgot to fill in something on the User Form. So you go back and put it in, then you click on the Control Button "Update_Engineer_Spec_10" and you get a run Time Error message. How can I get around this so that it updates the workbook? Do I need to add another Control Button to up date exsisting worksbooks? 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") 'Job Address Information .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value .Range("B12").Value = Me("State_1").Value .Range("C12").Value = Me("Zip_Code_1").Value More Code Here, but same as above. End With -- Dave Peterson . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error on file name
Here is the code to Save the Workbook
'Save Engineering Spec 11 Control Button 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 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 "Dave Peterson" wrote: This line doesn't look right. ..Range("A09").Value = Me("Office_1").Value Maybe you meant something like: ..Range("A09").Value = Me.controls("Office_1").Value =========== So somewhere you have a line that looks something like: With Workbooks("Master Engineering Spec.xlsm") .saveas filename:=..... .... Instead of refering to the workbook by its name, you can use a variable. Maybe even assign the variable when you open the workbook. dim wkbk as workbook .... set wkbk = workbooks.open(filename:="c:\somepath\Master Engineering Spec.xlsm") Then when you save it... wkbk.saveas filename:="C:\somenewname" But keep refering to that variable... with wkbk.workSheets("COVER SHEET") .Range("A09").Value = Me.controls("Office_1").Value Brian wrote: I have this code when I go to update a new workbook, but once the workbook is updated and saved, it will not allow you to reupdate it. I think the reason is because it's looking for the workbook "Master Engineering Spec.xlsm" and that is not the workbook name. Here is the sequence I am following. 1: Open User Form 2: Open New Engineering Spec (Control Button "Open_New_Engineer_Spec_8") 3: Fill in the user Form 4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10" 5: Save Engineering Spec workbook (I automaticaly assigns the name. Now here is where the problem is. You forgot to fill in something on the User Form. So you go back and put it in, then you click on the Control Button "Update_Engineer_Spec_10" and you get a run Time Error message. How can I get around this so that it updates the workbook? Do I need to add another Control Button to up date exsisting worksbooks? 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") 'Job Address Information .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value .Range("B12").Value = Me("State_1").Value .Range("C12").Value = Me("Zip_Code_1").Value More Code Here, but same as above. End With -- Dave Peterson . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error on file name
Here is the code to update the Workbook
'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") ' Misc Codes: .Range("L02").Value = Format(Spec_Date_2.Text, "mm-dd-yyyy") .Range("L03").Value = Me("Distribution_Code_1").Value .Range("L04").Value = Me("Material_Supplier_1").Value .Range("L05").Value = Me("Engineering_Supplier_1").Value .Range("L06").Value = Me("Installation_Supplier_1").Value Alot more here same as above End With 'Update Header Footnote Information Dim sh As Integer For sh = 1 To Sheets.Count With Sheets(sh).PageSetup .LeftHeader = "&8Cilli Code: " & CLLI_Code_1.Value _ & Chr(10) & "Office Name: " & Me.Office_1.Value .CenterHeader = "&8TEO Number: " & Me.TEO_No_1.Value _ & Chr(10) & "Supplier Order No: " & Me.CES_No_1.Value .RightHeader = "&8Page &P of &N" & Chr(10) _ & "Appendix No: " & Me.TEO_Appx_No_2.Value .CenterFooter = "&8RESTRICTED - PROPRIETARY INFORMATION" _ & Chr(10) & "Not for use or Disclosure outside ATT except under Written Agreement" .LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.55) .BottomMargin = Application.InchesToPoints(0.7) .HeaderMargin = Application.InchesToPoints(0.25) .FooterMargin = Application.InchesToPoints(0.25) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = -3 .CenterHorizontally = True .CenterVertically = True .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed .OddAndEvenPagesHeaderFooter = False .DifferentFirstPageHeaderFooter = False .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = True .EvenPage.LeftHeader.Text = "" End With Next sh '================================================ 'Update Data Storage Sheet (Hidden in Job Work Book) '================================================ With Workbooks("Master Engineering Spec.xlsm").Sheets("JOB DATA") ' Site Information: .Range("D02").Value = Me("CLLI_Code_1").Value .Range("D03").Value = Me("Office_1").Value .Range("D04").Value = Me("Address_11").Value .Range("D05").Value = Me("Address_12").Value .Range("D06").Value = Me("City_1").Value .Range("D07").Value = Me("State_1").Value .Range("D08").Value = Me("Zip_Code_1").Value Alot more here same as above End With End Sub "Dave Peterson" wrote: This line doesn't look right. ..Range("A09").Value = Me("Office_1").Value Maybe you meant something like: ..Range("A09").Value = Me.controls("Office_1").Value =========== So somewhere you have a line that looks something like: With Workbooks("Master Engineering Spec.xlsm") .saveas filename:=..... .... Instead of refering to the workbook by its name, you can use a variable. Maybe even assign the variable when you open the workbook. dim wkbk as workbook .... set wkbk = workbooks.open(filename:="c:\somepath\Master Engineering Spec.xlsm") Then when you save it... wkbk.saveas filename:="C:\somenewname" But keep refering to that variable... with wkbk.workSheets("COVER SHEET") .Range("A09").Value = Me.controls("Office_1").Value Brian wrote: I have this code when I go to update a new workbook, but once the workbook is updated and saved, it will not allow you to reupdate it. I think the reason is because it's looking for the workbook "Master Engineering Spec.xlsm" and that is not the workbook name. Here is the sequence I am following. 1: Open User Form 2: Open New Engineering Spec (Control Button "Open_New_Engineer_Spec_8") 3: Fill in the user Form 4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10" 5: Save Engineering Spec workbook (I automaticaly assigns the name. Now here is where the problem is. You forgot to fill in something on the User Form. So you go back and put it in, then you click on the Control Button "Update_Engineer_Spec_10" and you get a run Time Error message. How can I get around this so that it updates the workbook? Do I need to add another Control Button to up date exsisting worksbooks? 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") 'Job Address Information .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value .Range("B12").Value = Me("State_1").Value .Range("C12").Value = Me("Zip_Code_1").Value More Code Here, but same as above. End With -- Dave Peterson . |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error on file name
Is the activeworkbook the same as the workbook that was opened?
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. Brian wrote: Here is the code to Save the Workbook 'Save Engineering Spec 11 Control Button 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 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 "Dave Peterson" wrote: This line doesn't look right. ..Range("A09").Value = Me("Office_1").Value Maybe you meant something like: ..Range("A09").Value = Me.controls("Office_1").Value =========== So somewhere you have a line that looks something like: With Workbooks("Master Engineering Spec.xlsm") .saveas filename:=..... .... Instead of refering to the workbook by its name, you can use a variable. Maybe even assign the variable when you open the workbook. dim wkbk as workbook .... set wkbk = workbooks.open(filename:="c:\somepath\Master Engineering Spec.xlsm") Then when you save it... wkbk.saveas filename:="C:\somenewname" But keep refering to that variable... with wkbk.workSheets("COVER SHEET") .Range("A09").Value = Me.controls("Office_1").Value Brian wrote: I have this code when I go to update a new workbook, but once the workbook is updated and saved, it will not allow you to reupdate it. I think the reason is because it's looking for the workbook "Master Engineering Spec.xlsm" and that is not the workbook name. Here is the sequence I am following. 1: Open User Form 2: Open New Engineering Spec (Control Button "Open_New_Engineer_Spec_8") 3: Fill in the user Form 4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10" 5: Save Engineering Spec workbook (I automaticaly assigns the name. Now here is where the problem is. You forgot to fill in something on the User Form. So you go back and put it in, then you click on the Control Button "Update_Engineer_Spec_10" and you get a run Time Error message. How can I get around this so that it updates the workbook? Do I need to add another Control Button to up date exsisting worksbooks? 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") 'Job Address Information .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value .Range("B12").Value = Me("State_1").Value .Range("C12").Value = Me("Zip_Code_1").Value More Code Here, but same as above. End With -- Dave Peterson . -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error on file name
Like I said, I do not believe you have to change any file names. I believe
all you have to do is put a line of code in your SaveAs macro, after the SaveAs has executed, to open the "Master Engineering Spec.xlsm" again. What happens when you execute the SaveAs is: 1. The workbook you have just updated "Master Engineering Spec.xlsm" changes to 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 2. At this point, "Master Engineering Spec.xlsm" has, in effect, been closed. Look at the title bar when the SaveAs macro runs. You will see the name change. Also check the status bar at the bottom of the screen and you will no longer see the MES file listed as active. 3. The UserForm is operating from the new workbook saved under 1. above since you did not specify the file to save as .xlxs. The new workbook contains the entire code from the MES file. But if you try to reference any of the sheets from that file you will get the Subxcript out of Bounds error because the MES file is no longer open. 4. The simple solution is to re-open the file if you need to make a correction. 5. There is a possibility that you might have to close the UserForm and re-open it in the MES file to avoid error messages from that operation, although I am not completely sure that would occur, Better to be on the safe side. Remember that after the SaveAs, the UserForm was tied to the new workbook, so by opening the original MES file, closing unloading the UserForm and re-opening it, it should again be tied to the MES file. 6. Unless my understanding of how you have programmed this is all screwed up, you should then be able to make corrections on the form and re-do the SaveAs routine to update the job file. If you are now totally confused, go to bed, get some sleep. Come back tomorrow and read this again. It is not complicated, it just takes a lot of verbage to cover the details. "Brian" wrote in message ... Would changing the file name on the New Workbook "Master Engineering Spec.xlsm" to 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 Solve the Problem or would it be better to add another control button to update the StrFile? "JLGWhiz" wrote: BTW, as Dave pointed out, when you run the update and do a SaveAs operation, you effectively close the parent workbook, so the cure for the problem could be as simple as adding a line of code after the SaveAs command to re-open the "Master Engineering Spec.xlsm" workbook. Check that out and see if that will work. "Brian" wrote in message ... Everything works exactly as it should. When you open an exsisiting Work Book it fills in the User Form from the Hidden Sheet in the Work Book, but when you change or add anything to the User Form and click on Update again it gives you a run Time Error 9, Subscript out of range. 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") ERROR The only thing I can think of is that the file name is not ("Master Engineering Spec.xlsm"). Remember the file name is assigned as follows: 'Save Engineering Spec 11 Control Button 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 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 was wondering if adding another set of update buttons work fix this problem. There will be a set for New Work Books and a set for Exsisiting Work Books. The New Work Book will Look for the Name "Master Engineering Spec.xlsm". The Exsisitng Work Book will Look for the Name 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 Either that or change the New Work Book to the name as the same varible as a Exsisiting Work Book. That way no matter Which Work Book is open it will see the "Spec" in the name and update it. "JLGWhiz" wrote: Brian, I thought that was one of the functions of the button to repopulate the form, to allow for updates to previously entered data. Is this the part that is not working? I read Dave's answer about the different workbook name and that would present a problem if you are trying to recall the data from that workbook. But I had the impression that you were storing the data to recover in the master workbook and would then resave the updates in the job workbook. Maybe my concept was all screwed up. "Brian" wrote in message ... I have this code when I go to update a new workbook, but once the workbook is updated and saved, it will not allow you to reupdate it. I think the reason is because it's looking for the workbook "Master Engineering Spec.xlsm" and that is not the workbook name. Here is the sequence I am following. 1: Open User Form 2: Open New Engineering Spec (Control Button "Open_New_Engineer_Spec_8") 3: Fill in the user Form 4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10" 5: Save Engineering Spec workbook (I automaticaly assigns the name. Now here is where the problem is. You forgot to fill in something on the User Form. So you go back and put it in, then you click on the Control Button "Update_Engineer_Spec_10" and you get a run Time Error message. How can I get around this so that it updates the workbook? Do I need to add another Control Button to up date exsisting worksbooks? 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") 'Job Address Information .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value .Range("B12").Value = Me("State_1").Value .Range("C12").Value = Me("Zip_Code_1").Value More Code Here, but same as above. End With . . |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error on file name
Depends, if you open a New Workbook it is named "Master Engineering Spec.xlsm"
But after it is saved it is named "SPEC" & CLLI_Code_1.Value _ & Space(1) & TEO_No_1.Value _ & Space(1) & CES_No_1.Value _ & Space(1) & TEO_Appx_No_2.Value So what is happening is when you go to update the Workbook the 1st time before you save it works fine, but after you save the workbook the file name changes and it can not find it, so it gives the subscript out of range error. I see 2 solutions to this problem 1: Change the New Work Book Name to "SPEC" & CLLI_Code_1.Value _ & Space(1) & TEO_No_1.Value _ & Space(1) & CES_No_1.Value _ & Space(1) & TEO_Appx_No_2.Value 2: Add a second update button that looks for the name "SPEC" & CLLI_Code_1.Value _ & Space(1) & TEO_No_1.Value _ & Space(1) & CES_No_1.Value _ & Space(1) & TEO_Appx_No_2.Value What do you think would be the best or most efficent? "Dave Peterson" wrote: Is the activeworkbook the same as the workbook that was opened? 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. Brian wrote: Here is the code to Save the Workbook 'Save Engineering Spec 11 Control Button 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 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 "Dave Peterson" wrote: This line doesn't look right. ..Range("A09").Value = Me("Office_1").Value Maybe you meant something like: ..Range("A09").Value = Me.controls("Office_1").Value =========== So somewhere you have a line that looks something like: With Workbooks("Master Engineering Spec.xlsm") .saveas filename:=..... .... Instead of refering to the workbook by its name, you can use a variable. Maybe even assign the variable when you open the workbook. dim wkbk as workbook .... set wkbk = workbooks.open(filename:="c:\somepath\Master Engineering Spec.xlsm") Then when you save it... wkbk.saveas filename:="C:\somenewname" But keep refering to that variable... with wkbk.workSheets("COVER SHEET") .Range("A09").Value = Me.controls("Office_1").Value Brian wrote: I have this code when I go to update a new workbook, but once the workbook is updated and saved, it will not allow you to reupdate it. I think the reason is because it's looking for the workbook "Master Engineering Spec.xlsm" and that is not the workbook name. Here is the sequence I am following. 1: Open User Form 2: Open New Engineering Spec (Control Button "Open_New_Engineer_Spec_8") 3: Fill in the user Form 4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10" 5: Save Engineering Spec workbook (I automaticaly assigns the name. Now here is where the problem is. You forgot to fill in something on the User Form. So you go back and put it in, then you click on the Control Button "Update_Engineer_Spec_10" and you get a run Time Error message. How can I get around this so that it updates the workbook? Do I need to add another Control Button to up date exsisting worksbooks? 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") 'Job Address Information .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value .Range("B12").Value = Me("State_1").Value .Range("C12").Value = Me("Zip_Code_1").Value More Code Here, but same as above. End With -- Dave Peterson . -- Dave Peterson . |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error on file name
Look under Daves post and all the code is posted exactly as it is in my VBA,
I broke it down into each Sub. Look at the code and see how bad I broke it this. "JLGWhiz" wrote: Like I said, I do not believe you have to change any file names. I believe all you have to do is put a line of code in your SaveAs macro, after the SaveAs has executed, to open the "Master Engineering Spec.xlsm" again. What happens when you execute the SaveAs is: 1. The workbook you have just updated "Master Engineering Spec.xlsm" changes to 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 2. At this point, "Master Engineering Spec.xlsm" has, in effect, been closed. Look at the title bar when the SaveAs macro runs. You will see the name change. Also check the status bar at the bottom of the screen and you will no longer see the MES file listed as active. 3. The UserForm is operating from the new workbook saved under 1. above since you did not specify the file to save as .xlxs. The new workbook contains the entire code from the MES file. But if you try to reference any of the sheets from that file you will get the Subxcript out of Bounds error because the MES file is no longer open. 4. The simple solution is to re-open the file if you need to make a correction. 5. There is a possibility that you might have to close the UserForm and re-open it in the MES file to avoid error messages from that operation, although I am not completely sure that would occur, Better to be on the safe side. Remember that after the SaveAs, the UserForm was tied to the new workbook, so by opening the original MES file, closing unloading the UserForm and re-opening it, it should again be tied to the MES file. 6. Unless my understanding of how you have programmed this is all screwed up, you should then be able to make corrections on the form and re-do the SaveAs routine to update the job file. If you are now totally confused, go to bed, get some sleep. Come back tomorrow and read this again. It is not complicated, it just takes a lot of verbage to cover the details. "Brian" wrote in message ... Would changing the file name on the New Workbook "Master Engineering Spec.xlsm" to 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 Solve the Problem or would it be better to add another control button to update the StrFile? "JLGWhiz" wrote: BTW, as Dave pointed out, when you run the update and do a SaveAs operation, you effectively close the parent workbook, so the cure for the problem could be as simple as adding a line of code after the SaveAs command to re-open the "Master Engineering Spec.xlsm" workbook. Check that out and see if that will work. "Brian" wrote in message ... Everything works exactly as it should. When you open an exsisiting Work Book it fills in the User Form from the Hidden Sheet in the Work Book, but when you change or add anything to the User Form and click on Update again it gives you a run Time Error 9, Subscript out of range. 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") ERROR The only thing I can think of is that the file name is not ("Master Engineering Spec.xlsm"). Remember the file name is assigned as follows: 'Save Engineering Spec 11 Control Button 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 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 was wondering if adding another set of update buttons work fix this problem. There will be a set for New Work Books and a set for Exsisiting Work Books. The New Work Book will Look for the Name "Master Engineering Spec.xlsm". The Exsisitng Work Book will Look for the Name 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 Either that or change the New Work Book to the name as the same varible as a Exsisiting Work Book. That way no matter Which Work Book is open it will see the "Spec" in the name and update it. "JLGWhiz" wrote: Brian, I thought that was one of the functions of the button to repopulate the form, to allow for updates to previously entered data. Is this the part that is not working? I read Dave's answer about the different workbook name and that would present a problem if you are trying to recall the data from that workbook. But I had the impression that you were storing the data to recover in the master workbook and would then resave the updates in the job workbook. Maybe my concept was all screwed up. "Brian" wrote in message ... I have this code when I go to update a new workbook, but once the workbook is updated and saved, it will not allow you to reupdate it. I think the reason is because it's looking for the workbook "Master Engineering Spec.xlsm" and that is not the workbook name. Here is the sequence I am following. 1: Open User Form 2: Open New Engineering Spec (Control Button "Open_New_Engineer_Spec_8") 3: Fill in the user Form 4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10" 5: Save Engineering Spec workbook (I automaticaly assigns the name. Now here is where the problem is. You forgot to fill in something on the User Form. So you go back and put it in, then you click on the Control Button "Update_Engineer_Spec_10" and you get a run Time Error message. How can I get around this so that it updates the workbook? Do I need to add another Control Button to up date exsisting worksbooks? 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") 'Job Address Information .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value .Range("B12").Value = Me("State_1").Value .Range("C12").Value = Me("Zip_Code_1").Value More Code Here, but same as above. End With . . . |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error on file name
Again, I'd use the workbook variable.
Brian wrote: Depends, if you open a New Workbook it is named "Master Engineering Spec.xlsm" But after it is saved it is named "SPEC" & CLLI_Code_1.Value _ & Space(1) & TEO_No_1.Value _ & Space(1) & CES_No_1.Value _ & Space(1) & TEO_Appx_No_2.Value So what is happening is when you go to update the Workbook the 1st time before you save it works fine, but after you save the workbook the file name changes and it can not find it, so it gives the subscript out of range error. I see 2 solutions to this problem 1: Change the New Work Book Name to "SPEC" & CLLI_Code_1.Value _ & Space(1) & TEO_No_1.Value _ & Space(1) & CES_No_1.Value _ & Space(1) & TEO_Appx_No_2.Value 2: Add a second update button that looks for the name "SPEC" & CLLI_Code_1.Value _ & Space(1) & TEO_No_1.Value _ & Space(1) & CES_No_1.Value _ & Space(1) & TEO_Appx_No_2.Value What do you think would be the best or most efficent? "Dave Peterson" wrote: Is the activeworkbook the same as the workbook that was opened? 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. Brian wrote: Here is the code to Save the Workbook 'Save Engineering Spec 11 Control Button 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 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 "Dave Peterson" wrote: This line doesn't look right. ..Range("A09").Value = Me("Office_1").Value Maybe you meant something like: ..Range("A09").Value = Me.controls("Office_1").Value =========== So somewhere you have a line that looks something like: With Workbooks("Master Engineering Spec.xlsm") .saveas filename:=..... .... Instead of refering to the workbook by its name, you can use a variable. Maybe even assign the variable when you open the workbook. dim wkbk as workbook .... set wkbk = workbooks.open(filename:="c:\somepath\Master Engineering Spec.xlsm") Then when you save it... wkbk.saveas filename:="C:\somenewname" But keep refering to that variable... with wkbk.workSheets("COVER SHEET") .Range("A09").Value = Me.controls("Office_1").Value Brian wrote: I have this code when I go to update a new workbook, but once the workbook is updated and saved, it will not allow you to reupdate it. I think the reason is because it's looking for the workbook "Master Engineering Spec.xlsm" and that is not the workbook name. Here is the sequence I am following. 1: Open User Form 2: Open New Engineering Spec (Control Button "Open_New_Engineer_Spec_8") 3: Fill in the user Form 4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10" 5: Save Engineering Spec workbook (I automaticaly assigns the name. Now here is where the problem is. You forgot to fill in something on the User Form. So you go back and put it in, then you click on the Control Button "Update_Engineer_Spec_10" and you get a run Time Error message. How can I get around this so that it updates the workbook? Do I need to add another Control Button to up date exsisting worksbooks? 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") 'Job Address Information .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value .Range("B12").Value = Me("State_1").Value .Range("C12").Value = Me("Zip_Code_1").Value More Code Here, but same as above. End With -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error on file name
I think the solution is to change the new workbook reference from "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 Either that or make it look for both. What do you think? "JLGWhiz" wrote: Like I said, I do not believe you have to change any file names. I believe all you have to do is put a line of code in your SaveAs macro, after the SaveAs has executed, to open the "Master Engineering Spec.xlsm" again. What happens when you execute the SaveAs is: 1. The workbook you have just updated "Master Engineering Spec.xlsm" changes to 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 2. At this point, "Master Engineering Spec.xlsm" has, in effect, been closed. Look at the title bar when the SaveAs macro runs. You will see the name change. Also check the status bar at the bottom of the screen and you will no longer see the MES file listed as active. 3. The UserForm is operating from the new workbook saved under 1. above since you did not specify the file to save as .xlxs. The new workbook contains the entire code from the MES file. But if you try to reference any of the sheets from that file you will get the Subxcript out of Bounds error because the MES file is no longer open. 4. The simple solution is to re-open the file if you need to make a correction. 5. There is a possibility that you might have to close the UserForm and re-open it in the MES file to avoid error messages from that operation, although I am not completely sure that would occur, Better to be on the safe side. Remember that after the SaveAs, the UserForm was tied to the new workbook, so by opening the original MES file, closing unloading the UserForm and re-opening it, it should again be tied to the MES file. 6. Unless my understanding of how you have programmed this is all screwed up, you should then be able to make corrections on the form and re-do the SaveAs routine to update the job file. If you are now totally confused, go to bed, get some sleep. Come back tomorrow and read this again. It is not complicated, it just takes a lot of verbage to cover the details. "Brian" wrote in message ... Would changing the file name on the New Workbook "Master Engineering Spec.xlsm" to 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 Solve the Problem or would it be better to add another control button to update the StrFile? "JLGWhiz" wrote: BTW, as Dave pointed out, when you run the update and do a SaveAs operation, you effectively close the parent workbook, so the cure for the problem could be as simple as adding a line of code after the SaveAs command to re-open the "Master Engineering Spec.xlsm" workbook. Check that out and see if that will work. "Brian" wrote in message ... Everything works exactly as it should. When you open an exsisiting Work Book it fills in the User Form from the Hidden Sheet in the Work Book, but when you change or add anything to the User Form and click on Update again it gives you a run Time Error 9, Subscript out of range. 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") ERROR The only thing I can think of is that the file name is not ("Master Engineering Spec.xlsm"). Remember the file name is assigned as follows: 'Save Engineering Spec 11 Control Button 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 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 was wondering if adding another set of update buttons work fix this problem. There will be a set for New Work Books and a set for Exsisiting Work Books. The New Work Book will Look for the Name "Master Engineering Spec.xlsm". The Exsisitng Work Book will Look for the Name 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 Either that or change the New Work Book to the name as the same varible as a Exsisiting Work Book. That way no matter Which Work Book is open it will see the "Spec" in the name and update it. "JLGWhiz" wrote: Brian, I thought that was one of the functions of the button to repopulate the form, to allow for updates to previously entered data. Is this the part that is not working? I read Dave's answer about the different workbook name and that would present a problem if you are trying to recall the data from that workbook. But I had the impression that you were storing the data to recover in the master workbook and would then resave the updates in the job workbook. Maybe my concept was all screwed up. "Brian" wrote in message ... I have this code when I go to update a new workbook, but once the workbook is updated and saved, it will not allow you to reupdate it. I think the reason is because it's looking for the workbook "Master Engineering Spec.xlsm" and that is not the workbook name. Here is the sequence I am following. 1: Open User Form 2: Open New Engineering Spec (Control Button "Open_New_Engineer_Spec_8") 3: Fill in the user Form 4: Update Engineering Spec (Control Button "Update_Engineer_Spec_10" 5: Save Engineering Spec workbook (I automaticaly assigns the name. Now here is where the problem is. You forgot to fill in something on the User Form. So you go back and put it in, then you click on the Control Button "Update_Engineer_Spec_10" and you get a run Time Error message. How can I get around this so that it updates the workbook? Do I need to add another Control Button to up date exsisting worksbooks? 'Update Engineering Spec Control Button(Sheet 1) Private Sub Update_Engineer_Spec_10_Click() With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") 'Job Address Information .Range("A09").Value = Me("Office_1").Value .Range("A10").Value = Me("Address_11").Value .Range("A11").Value = Me("Address_12").Value .Range("A12").Value = Me("City_1").Value .Range("B12").Value = Me("State_1").Value .Range("C12").Value = Me("Zip_Code_1").Value More Code Here, but same as above. End With . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run time error 53, cannot find file | Excel Programming | |||
HELP=VB 'File Already Open' ERROR (run-time 55) | Excel Programming | |||
Export a chart in a GIF file. Run-time error '1004': Application-defined or object-defined error; | Excel Programming | |||
Appending to a file with run-time error '54' Bad file mode | Excel Programming | |||
Run-time error 75 Path/File access error | Excel Programming |