Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help in changing code to see new file name (Dave Peterson)
Dave can you help me with this. I am not exactly sure what you mean.
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 in changing code to see new file name (Dave Peterson)
In a general module:
Option Explicit Public bk as workbook In your userform: 'procedure to open the file: set bk = workbooks.open(....) In the procedure that saves the file. bk.saveas filename:=... 'then to prove that the variable is still working 'no matter what the name changed to msgbox bk.fullname Brian wrote: Dave can you help me with this. I am not exactly sure what you mean. 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. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help in changing code to see new file name (Dave Peterson)
Everything works perfect except for the Update. I was playing around with and
it seems as long as the workbook name stays "Master Engineering Spec.xlsm" it works great, but as soon as you save the Workbook and the name changes it gives the Run Time error on this statement: With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") Cause it's looking for a filename that is not open. Remeber the name when the workbook was open was "Master Engineering Spec.xlsm", but after the workbook was saved the name was changed 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 Would it just be easier to change the New Workbook name to the same format as the exsisiting workbook name or would it still have the same problem? I am still not exactly sure where to place the code. I posted all the code prior to the site going down. Do you need me to repost all the code again for you? If so I will in a new post. "Dave Peterson" wrote: In a general module: Option Explicit Public bk as workbook In your userform: 'procedure to open the file: set bk = workbooks.open(....) In the procedure that saves the file. bk.saveas filename:=... 'then to prove that the variable is still working 'no matter what the name changed to msgbox bk.fullname Brian wrote: Dave can you help me with this. I am not exactly sure what you mean. 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. -- Dave Peterson . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help in changing code to see new file name (Dave Peterson)
That's why I keep suggesting using a variable:
With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") could be With bk.Sheets("COVER SHEET") You don't need to post the entire code for me. My suggestion won't change. Brian wrote: Everything works perfect except for the Update. I was playing around with and it seems as long as the workbook name stays "Master Engineering Spec.xlsm" it works great, but as soon as you save the Workbook and the name changes it gives the Run Time error on this statement: With Workbooks("Master Engineering Spec.xlsm").Sheets("COVER SHEET") Cause it's looking for a filename that is not open. Remeber the name when the workbook was open was "Master Engineering Spec.xlsm", but after the workbook was saved the name was changed 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 Would it just be easier to change the New Workbook name to the same format as the exsisiting workbook name or would it still have the same problem? I am still not exactly sure where to place the code. I posted all the code prior to the site going down. Do you need me to repost all the code again for you? If so I will in a new post. "Dave Peterson" wrote: In a general module: Option Explicit Public bk as workbook In your userform: 'procedure to open the file: set bk = workbooks.open(....) In the procedure that saves the file. bk.saveas filename:=... 'then to prove that the variable is still working 'no matter what the name changed to msgbox bk.fullname Brian wrote: Dave can you help me with this. I am not exactly sure what you mean. 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. -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To: Dave Peterson | Excel Discussion (Misc queries) | |||
Dave Peterson | Excel Discussion (Misc queries) | |||
Dave Peterson... | Excel Programming | |||
to Dave Peterson | Excel Discussion (Misc queries) | |||
Dir () code -- Dave Peterson | Excel Programming |