|
Automatic File Name Generation
I am trying to write a Work Order entry form in Excel to simply a process and
automate what seems to be difficult for folks around here. I have a Work Order form that has a number of data fill-in cells with other cells protected. What I would like to do is two fold: 1) Have the spreadsheet automatically place a number in a cell called "WorkOrderNum" that would be one more incrementally than the last file saved. 2) Have the Work Order worksheet automatically use that WorkOrderNum number as the file name when it is saved. The functionality is such that i'm hoping to have the non-computer users here be able to open a single file which will automatically number itself as outlined above and then save with the number for later reference if necessary. Personally, I'd prefer using a single database with all these numbers and worksheet data saved in each row/record. But, knowing how others around here work, I don't think that will be possible. I've poked through the Help system and read several entries here in the forums but don't see exactly what I'm looking for. Any help or suggestions are appreciated. -- Steven Leuck Builders Electric, Inc. |
Automatic File Name Generation
Lets see if this variation of a theme from J.E. McGimpsey won't meet your
needs. Use Notepad to create a file to contain your work order number. "Seed" it with the value of the last used work order number. Save the file in the same folder with your work order Excel file. For my example I called the file WONums.txt This code must go into the Workbook's _Open() event handler. To put it there, open your work order workbook and right-click the Excel icon that is immediately to the left of the word [File] in the menu toolbar and choose [View Code] from the pop up list. Copy the code below and paste it into the code window in the VB Editor that shows up. Close the VB Editor, save your file and close it. Next time you open it, the number in WONums.txt will be obtained, incremented and placed into WorkOrderNum. In the code you'll see the line that does that and I've assumed that the worksheet with that named range is named WorkOrder. Change that sheet name as required for your workbook. The last thing the routine does is save the workbook out into the same folder using the work order # created as the filename. Private Sub Workbook_Open() Dim WONumberFile As String Dim WONumbuffer As Integer Dim WONum As Long 'file WONums.txt must be in same folder 'with this Excel file 'find where this file is at and use 'that path to find the WONums.txt file WONumberFile = Left(Me.FullName, _ InStrRev(Me.FullName, Application.PathSeparator)) _ & "WONums.txt" WONumbuffer = FreeFile() 'get the last Work Order Number used Open WONumberFile For Input As #WONumbuffer Input #WONumbuffer, WONum Close #WONumbuffer 'increment last W.O.# WONum = WONum + 1 'place new W.O.# on worksheet '***** change worksheet name as required **** Worksheets("WorkOrder").Range("WorkOrderNum") = WONum 'write the new number back to the WONums.txt file WONumbuffer = FreeFile() Open WONumberFile For Output As #WONumbuffer Print #WONumbuffer, WONum Close #WONumbuffer 'build new name for this file based on 'the assigned W.O.# WONumberFile = Left(WONumberFile, _ InStrRev(WONumberFile, Application.PathSeparator)) _ & Trim(Str(WONum)) & ".xls" 'save the workbook under the new name ThisWorkbook.SaveAs WONumberFile End Sub "Steven Leuck" wrote: I am trying to write a Work Order entry form in Excel to simply a process and automate what seems to be difficult for folks around here. I have a Work Order form that has a number of data fill-in cells with other cells protected. What I would like to do is two fold: 1) Have the spreadsheet automatically place a number in a cell called "WorkOrderNum" that would be one more incrementally than the last file saved. 2) Have the Work Order worksheet automatically use that WorkOrderNum number as the file name when it is saved. The functionality is such that i'm hoping to have the non-computer users here be able to open a single file which will automatically number itself as outlined above and then save with the number for later reference if necessary. Personally, I'd prefer using a single database with all these numbers and worksheet data saved in each row/record. But, knowing how others around here work, I don't think that will be possible. I've poked through the Help system and read several entries here in the forums but don't see exactly what I'm looking for. Any help or suggestions are appreciated. -- Steven Leuck Builders Electric, Inc. |
Automatic File Name Generation
I too would like to accomplish the same task as Steven Leuck, however I am
having trouble. I believe I followed exactly what you indicated but I keep getting a Runtime Error 9. The line listed here is highlighted in yellow in the error message. Worksheets("WorkOrder").Range("WorkOrderNum") = WONum Not sure what I am doing wrong, I would appreciate some help. Could I send you my file? Thank you, Dane Watson "JLatham" wrote: Lets see if this variation of a theme from J.E. McGimpsey won't meet your needs. Use Notepad to create a file to contain your work order number. "Seed" it with the value of the last used work order number. Save the file in the same folder with your work order Excel file. For my example I called the file WONums.txt This code must go into the Workbook's _Open() event handler. To put it there, open your work order workbook and right-click the Excel icon that is immediately to the left of the word [File] in the menu toolbar and choose [View Code] from the pop up list. Copy the code below and paste it into the code window in the VB Editor that shows up. Close the VB Editor, save your file and close it. Next time you open it, the number in WONums.txt will be obtained, incremented and placed into WorkOrderNum. In the code you'll see the line that does that and I've assumed that the worksheet with that named range is named WorkOrder. Change that sheet name as required for your workbook. The last thing the routine does is save the workbook out into the same folder using the work order # created as the filename. Private Sub Workbook_Open() Dim WONumberFile As String Dim WONumbuffer As Integer Dim WONum As Long 'file WONums.txt must be in same folder 'with this Excel file 'find where this file is at and use 'that path to find the WONums.txt file WONumberFile = Left(Me.FullName, _ InStrRev(Me.FullName, Application.PathSeparator)) _ & "WONums.txt" WONumbuffer = FreeFile() 'get the last Work Order Number used Open WONumberFile For Input As #WONumbuffer Input #WONumbuffer, WONum Close #WONumbuffer 'increment last W.O.# WONum = WONum + 1 'place new W.O.# on worksheet '***** change worksheet name as required **** Worksheets("WorkOrder").Range("WorkOrderNum") = WONum 'write the new number back to the WONums.txt file WONumbuffer = FreeFile() Open WONumberFile For Output As #WONumbuffer Print #WONumbuffer, WONum Close #WONumbuffer 'build new name for this file based on 'the assigned W.O.# WONumberFile = Left(WONumberFile, _ InStrRev(WONumberFile, Application.PathSeparator)) _ & Trim(Str(WONum)) & ".xls" 'save the workbook under the new name ThisWorkbook.SaveAs WONumberFile End Sub "Steven Leuck" wrote: I am trying to write a Work Order entry form in Excel to simply a process and automate what seems to be difficult for folks around here. I have a Work Order form that has a number of data fill-in cells with other cells protected. What I would like to do is two fold: 1) Have the spreadsheet automatically place a number in a cell called "WorkOrderNum" that would be one more incrementally than the last file saved. 2) Have the Work Order worksheet automatically use that WorkOrderNum number as the file name when it is saved. The functionality is such that i'm hoping to have the non-computer users here be able to open a single file which will automatically number itself as outlined above and then save with the number for later reference if necessary. Personally, I'd prefer using a single database with all these numbers and worksheet data saved in each row/record. But, knowing how others around here work, I don't think that will be possible. I've poked through the Help system and read several entries here in the forums but don't see exactly what I'm looking for. Any help or suggestions are appreciated. -- Steven Leuck Builders Electric, Inc. |
Automatic File Name Generation
I'm not Jerry...
Do you have a worksheet in the activeworkbook named WorkOrder? I bet not (check for leading/trailing spaces if you think you do). Dane wrote: I too would like to accomplish the same task as Steven Leuck, however I am having trouble. I believe I followed exactly what you indicated but I keep getting a Runtime Error 9. The line listed here is highlighted in yellow in the error message. Worksheets("WorkOrder").Range("WorkOrderNum") = WONum Not sure what I am doing wrong, I would appreciate some help. Could I send you my file? Thank you, Dane Watson "JLatham" wrote: Lets see if this variation of a theme from J.E. McGimpsey won't meet your needs. Use Notepad to create a file to contain your work order number. "Seed" it with the value of the last used work order number. Save the file in the same folder with your work order Excel file. For my example I called the file WONums.txt This code must go into the Workbook's _Open() event handler. To put it there, open your work order workbook and right-click the Excel icon that is immediately to the left of the word [File] in the menu toolbar and choose [View Code] from the pop up list. Copy the code below and paste it into the code window in the VB Editor that shows up. Close the VB Editor, save your file and close it. Next time you open it, the number in WONums.txt will be obtained, incremented and placed into WorkOrderNum. In the code you'll see the line that does that and I've assumed that the worksheet with that named range is named WorkOrder. Change that sheet name as required for your workbook. The last thing the routine does is save the workbook out into the same folder using the work order # created as the filename. Private Sub Workbook_Open() Dim WONumberFile As String Dim WONumbuffer As Integer Dim WONum As Long 'file WONums.txt must be in same folder 'with this Excel file 'find where this file is at and use 'that path to find the WONums.txt file WONumberFile = Left(Me.FullName, _ InStrRev(Me.FullName, Application.PathSeparator)) _ & "WONums.txt" WONumbuffer = FreeFile() 'get the last Work Order Number used Open WONumberFile For Input As #WONumbuffer Input #WONumbuffer, WONum Close #WONumbuffer 'increment last W.O.# WONum = WONum + 1 'place new W.O.# on worksheet '***** change worksheet name as required **** Worksheets("WorkOrder").Range("WorkOrderNum") = WONum 'write the new number back to the WONums.txt file WONumbuffer = FreeFile() Open WONumberFile For Output As #WONumbuffer Print #WONumbuffer, WONum Close #WONumbuffer 'build new name for this file based on 'the assigned W.O.# WONumberFile = Left(WONumberFile, _ InStrRev(WONumberFile, Application.PathSeparator)) _ & Trim(Str(WONum)) & ".xls" 'save the workbook under the new name ThisWorkbook.SaveAs WONumberFile End Sub "Steven Leuck" wrote: I am trying to write a Work Order entry form in Excel to simply a process and automate what seems to be difficult for folks around here. I have a Work Order form that has a number of data fill-in cells with other cells protected. What I would like to do is two fold: 1) Have the spreadsheet automatically place a number in a cell called "WorkOrderNum" that would be one more incrementally than the last file saved. 2) Have the Work Order worksheet automatically use that WorkOrderNum number as the file name when it is saved. The functionality is such that i'm hoping to have the non-computer users here be able to open a single file which will automatically number itself as outlined above and then save with the number for later reference if necessary. Personally, I'd prefer using a single database with all these numbers and worksheet data saved in each row/record. But, knowing how others around here work, I don't think that will be possible. I've poked through the Help system and read several entries here in the forums but don't see exactly what I'm looking for. Any help or suggestions are appreciated. -- Steven Leuck Builders Electric, Inc. -- Dave Peterson |
Automatic File Name Generation
Dave's suggestion is the same one I would have made. Check the name on the
sheet tab and make sure it is exactly the same as you've typed in your code. It is not all that unusual to find that the name on the sheet's tab is either " WorkOrder" or "WorkOrder " instead of actually just "WorkOrder". "Dane" wrote: I too would like to accomplish the same task as Steven Leuck, however I am having trouble. I believe I followed exactly what you indicated but I keep getting a Runtime Error 9. The line listed here is highlighted in yellow in the error message. Worksheets("WorkOrder").Range("WorkOrderNum") = WONum Not sure what I am doing wrong, I would appreciate some help. Could I send you my file? Thank you, Dane Watson "JLatham" wrote: Lets see if this variation of a theme from J.E. McGimpsey won't meet your needs. Use Notepad to create a file to contain your work order number. "Seed" it with the value of the last used work order number. Save the file in the same folder with your work order Excel file. For my example I called the file WONums.txt This code must go into the Workbook's _Open() event handler. To put it there, open your work order workbook and right-click the Excel icon that is immediately to the left of the word [File] in the menu toolbar and choose [View Code] from the pop up list. Copy the code below and paste it into the code window in the VB Editor that shows up. Close the VB Editor, save your file and close it. Next time you open it, the number in WONums.txt will be obtained, incremented and placed into WorkOrderNum. In the code you'll see the line that does that and I've assumed that the worksheet with that named range is named WorkOrder. Change that sheet name as required for your workbook. The last thing the routine does is save the workbook out into the same folder using the work order # created as the filename. Private Sub Workbook_Open() Dim WONumberFile As String Dim WONumbuffer As Integer Dim WONum As Long 'file WONums.txt must be in same folder 'with this Excel file 'find where this file is at and use 'that path to find the WONums.txt file WONumberFile = Left(Me.FullName, _ InStrRev(Me.FullName, Application.PathSeparator)) _ & "WONums.txt" WONumbuffer = FreeFile() 'get the last Work Order Number used Open WONumberFile For Input As #WONumbuffer Input #WONumbuffer, WONum Close #WONumbuffer 'increment last W.O.# WONum = WONum + 1 'place new W.O.# on worksheet '***** change worksheet name as required **** Worksheets("WorkOrder").Range("WorkOrderNum") = WONum 'write the new number back to the WONums.txt file WONumbuffer = FreeFile() Open WONumberFile For Output As #WONumbuffer Print #WONumbuffer, WONum Close #WONumbuffer 'build new name for this file based on 'the assigned W.O.# WONumberFile = Left(WONumberFile, _ InStrRev(WONumberFile, Application.PathSeparator)) _ & Trim(Str(WONum)) & ".xls" 'save the workbook under the new name ThisWorkbook.SaveAs WONumberFile End Sub "Steven Leuck" wrote: I am trying to write a Work Order entry form in Excel to simply a process and automate what seems to be difficult for folks around here. I have a Work Order form that has a number of data fill-in cells with other cells protected. What I would like to do is two fold: 1) Have the spreadsheet automatically place a number in a cell called "WorkOrderNum" that would be one more incrementally than the last file saved. 2) Have the Work Order worksheet automatically use that WorkOrderNum number as the file name when it is saved. The functionality is such that i'm hoping to have the non-computer users here be able to open a single file which will automatically number itself as outlined above and then save with the number for later reference if necessary. Personally, I'd prefer using a single database with all these numbers and worksheet data saved in each row/record. But, knowing how others around here work, I don't think that will be possible. I've poked through the Help system and read several entries here in the forums but don't see exactly what I'm looking for. Any help or suggestions are appreciated. -- Steven Leuck Builders Electric, Inc. |
Automatic File Name Generation
Thank you Dave,
I am afraid to say that I did not have the WORKS SHEET named WorkOrder, it was the default Sheet1. I since changed it, but the problem still occurs. "Dave Peterson" wrote: I'm not Jerry... Do you have a worksheet in the activeworkbook named WorkOrder? I bet not (check for leading/trailing spaces if you think you do). Dane wrote: I too would like to accomplish the same task as Steven Leuck, however I am having trouble. I believe I followed exactly what you indicated but I keep getting a Runtime Error 9. The line listed here is highlighted in yellow in the error message. Worksheets("WorkOrder").Range("WorkOrderNum") = WONum Not sure what I am doing wrong, I would appreciate some help. Could I send you my file? Thank you, Dane Watson "JLatham" wrote: Lets see if this variation of a theme from J.E. McGimpsey won't meet your needs. Use Notepad to create a file to contain your work order number. "Seed" it with the value of the last used work order number. Save the file in the same folder with your work order Excel file. For my example I called the file WONums.txt This code must go into the Workbook's _Open() event handler. To put it there, open your work order workbook and right-click the Excel icon that is immediately to the left of the word [File] in the menu toolbar and choose [View Code] from the pop up list. Copy the code below and paste it into the code window in the VB Editor that shows up. Close the VB Editor, save your file and close it. Next time you open it, the number in WONums.txt will be obtained, incremented and placed into WorkOrderNum. In the code you'll see the line that does that and I've assumed that the worksheet with that named range is named WorkOrder. Change that sheet name as required for your workbook. The last thing the routine does is save the workbook out into the same folder using the work order # created as the filename. Private Sub Workbook_Open() Dim WONumberFile As String Dim WONumbuffer As Integer Dim WONum As Long 'file WONums.txt must be in same folder 'with this Excel file 'find where this file is at and use 'that path to find the WONums.txt file WONumberFile = Left(Me.FullName, _ InStrRev(Me.FullName, Application.PathSeparator)) _ & "WONums.txt" WONumbuffer = FreeFile() 'get the last Work Order Number used Open WONumberFile For Input As #WONumbuffer Input #WONumbuffer, WONum Close #WONumbuffer 'increment last W.O.# WONum = WONum + 1 'place new W.O.# on worksheet '***** change worksheet name as required **** Worksheets("WorkOrder").Range("WorkOrderNum") = WONum 'write the new number back to the WONums.txt file WONumbuffer = FreeFile() Open WONumberFile For Output As #WONumbuffer Print #WONumbuffer, WONum Close #WONumbuffer 'build new name for this file based on 'the assigned W.O.# WONumberFile = Left(WONumberFile, _ InStrRev(WONumberFile, Application.PathSeparator)) _ & Trim(Str(WONum)) & ".xls" 'save the workbook under the new name ThisWorkbook.SaveAs WONumberFile End Sub "Steven Leuck" wrote: I am trying to write a Work Order entry form in Excel to simply a process and automate what seems to be difficult for folks around here. I have a Work Order form that has a number of data fill-in cells with other cells protected. What I would like to do is two fold: 1) Have the spreadsheet automatically place a number in a cell called "WorkOrderNum" that would be one more incrementally than the last file saved. 2) Have the Work Order worksheet automatically use that WorkOrderNum number as the file name when it is saved. The functionality is such that i'm hoping to have the non-computer users here be able to open a single file which will automatically number itself as outlined above and then save with the number for later reference if necessary. Personally, I'd prefer using a single database with all these numbers and worksheet data saved in each row/record. But, knowing how others around here work, I don't think that will be possible. I've poked through the Help system and read several entries here in the forums but don't see exactly what I'm looking for. Any help or suggestions are appreciated. -- Steven Leuck Builders Electric, Inc. -- Dave Peterson |
Automatic File Name Generation
Hi Jerry I just replied to Dave, as you can see I am still having the same
problem after making the corrections to the name of the work sheet. I was also very careful to ensure that there were no leading or trailing spaces. "JLatham" wrote: Dave's suggestion is the same one I would have made. Check the name on the sheet tab and make sure it is exactly the same as you've typed in your code. It is not all that unusual to find that the name on the sheet's tab is either " WorkOrder" or "WorkOrder " instead of actually just "WorkOrder". "Dane" wrote: I too would like to accomplish the same task as Steven Leuck, however I am having trouble. I believe I followed exactly what you indicated but I keep getting a Runtime Error 9. The line listed here is highlighted in yellow in the error message. Worksheets("WorkOrder").Range("WorkOrderNum") = WONum Not sure what I am doing wrong, I would appreciate some help. Could I send you my file? Thank you, Dane Watson "JLatham" wrote: Lets see if this variation of a theme from J.E. McGimpsey won't meet your needs. Use Notepad to create a file to contain your work order number. "Seed" it with the value of the last used work order number. Save the file in the same folder with your work order Excel file. For my example I called the file WONums.txt This code must go into the Workbook's _Open() event handler. To put it there, open your work order workbook and right-click the Excel icon that is immediately to the left of the word [File] in the menu toolbar and choose [View Code] from the pop up list. Copy the code below and paste it into the code window in the VB Editor that shows up. Close the VB Editor, save your file and close it. Next time you open it, the number in WONums.txt will be obtained, incremented and placed into WorkOrderNum. In the code you'll see the line that does that and I've assumed that the worksheet with that named range is named WorkOrder. Change that sheet name as required for your workbook. The last thing the routine does is save the workbook out into the same folder using the work order # created as the filename. Private Sub Workbook_Open() Dim WONumberFile As String Dim WONumbuffer As Integer Dim WONum As Long 'file WONums.txt must be in same folder 'with this Excel file 'find where this file is at and use 'that path to find the WONums.txt file WONumberFile = Left(Me.FullName, _ InStrRev(Me.FullName, Application.PathSeparator)) _ & "WONums.txt" WONumbuffer = FreeFile() 'get the last Work Order Number used Open WONumberFile For Input As #WONumbuffer Input #WONumbuffer, WONum Close #WONumbuffer 'increment last W.O.# WONum = WONum + 1 'place new W.O.# on worksheet '***** change worksheet name as required **** Worksheets("WorkOrder").Range("WorkOrderNum") = WONum 'write the new number back to the WONums.txt file WONumbuffer = FreeFile() Open WONumberFile For Output As #WONumbuffer Print #WONumbuffer, WONum Close #WONumbuffer 'build new name for this file based on 'the assigned W.O.# WONumberFile = Left(WONumberFile, _ InStrRev(WONumberFile, Application.PathSeparator)) _ & Trim(Str(WONum)) & ".xls" 'save the workbook under the new name ThisWorkbook.SaveAs WONumberFile End Sub "Steven Leuck" wrote: I am trying to write a Work Order entry form in Excel to simply a process and automate what seems to be difficult for folks around here. I have a Work Order form that has a number of data fill-in cells with other cells protected. What I would like to do is two fold: 1) Have the spreadsheet automatically place a number in a cell called "WorkOrderNum" that would be one more incrementally than the last file saved. 2) Have the Work Order worksheet automatically use that WorkOrderNum number as the file name when it is saved. The functionality is such that i'm hoping to have the non-computer users here be able to open a single file which will automatically number itself as outlined above and then save with the number for later reference if necessary. Personally, I'd prefer using a single database with all these numbers and worksheet data saved in each row/record. But, knowing how others around here work, I don't think that will be possible. I've poked through the Help system and read several entries here in the forums but don't see exactly what I'm looking for. Any help or suggestions are appreciated. -- Steven Leuck Builders Electric, Inc. |
Automatic File Name Generation
email me the workbook and I'll see if I can figure out what is not there that
needs to be. Make sure that you also have a range named WorkOrderNum in the workbook. email to (remove spaces) HelpFrom @ jlathamsite.com and I'll get it. "Dane" wrote: Hi Jerry I just replied to Dave, as you can see I am still having the same problem after making the corrections to the name of the work sheet. I was also very careful to ensure that there were no leading or trailing spaces. "JLatham" wrote: Dave's suggestion is the same one I would have made. Check the name on the sheet tab and make sure it is exactly the same as you've typed in your code. It is not all that unusual to find that the name on the sheet's tab is either " WorkOrder" or "WorkOrder " instead of actually just "WorkOrder". "Dane" wrote: I too would like to accomplish the same task as Steven Leuck, however I am having trouble. I believe I followed exactly what you indicated but I keep getting a Runtime Error 9. The line listed here is highlighted in yellow in the error message. Worksheets("WorkOrder").Range("WorkOrderNum") = WONum Not sure what I am doing wrong, I would appreciate some help. Could I send you my file? Thank you, Dane Watson "JLatham" wrote: Lets see if this variation of a theme from J.E. McGimpsey won't meet your needs. Use Notepad to create a file to contain your work order number. "Seed" it with the value of the last used work order number. Save the file in the same folder with your work order Excel file. For my example I called the file WONums.txt This code must go into the Workbook's _Open() event handler. To put it there, open your work order workbook and right-click the Excel icon that is immediately to the left of the word [File] in the menu toolbar and choose [View Code] from the pop up list. Copy the code below and paste it into the code window in the VB Editor that shows up. Close the VB Editor, save your file and close it. Next time you open it, the number in WONums.txt will be obtained, incremented and placed into WorkOrderNum. In the code you'll see the line that does that and I've assumed that the worksheet with that named range is named WorkOrder. Change that sheet name as required for your workbook. The last thing the routine does is save the workbook out into the same folder using the work order # created as the filename. Private Sub Workbook_Open() Dim WONumberFile As String Dim WONumbuffer As Integer Dim WONum As Long 'file WONums.txt must be in same folder 'with this Excel file 'find where this file is at and use 'that path to find the WONums.txt file WONumberFile = Left(Me.FullName, _ InStrRev(Me.FullName, Application.PathSeparator)) _ & "WONums.txt" WONumbuffer = FreeFile() 'get the last Work Order Number used Open WONumberFile For Input As #WONumbuffer Input #WONumbuffer, WONum Close #WONumbuffer 'increment last W.O.# WONum = WONum + 1 'place new W.O.# on worksheet '***** change worksheet name as required **** Worksheets("WorkOrder").Range("WorkOrderNum") = WONum 'write the new number back to the WONums.txt file WONumbuffer = FreeFile() Open WONumberFile For Output As #WONumbuffer Print #WONumbuffer, WONum Close #WONumbuffer 'build new name for this file based on 'the assigned W.O.# WONumberFile = Left(WONumberFile, _ InStrRev(WONumberFile, Application.PathSeparator)) _ & Trim(Str(WONum)) & ".xls" 'save the workbook under the new name ThisWorkbook.SaveAs WONumberFile End Sub "Steven Leuck" wrote: I am trying to write a Work Order entry form in Excel to simply a process and automate what seems to be difficult for folks around here. I have a Work Order form that has a number of data fill-in cells with other cells protected. What I would like to do is two fold: 1) Have the spreadsheet automatically place a number in a cell called "WorkOrderNum" that would be one more incrementally than the last file saved. 2) Have the Work Order worksheet automatically use that WorkOrderNum number as the file name when it is saved. The functionality is such that i'm hoping to have the non-computer users here be able to open a single file which will automatically number itself as outlined above and then save with the number for later reference if necessary. Personally, I'd prefer using a single database with all these numbers and worksheet data saved in each row/record. But, knowing how others around here work, I don't think that will be possible. I've poked through the Help system and read several entries here in the forums but don't see exactly what I'm looking for. Any help or suggestions are appreciated. -- Steven Leuck Builders Electric, Inc. |
Automatic File Name Generation
Hi Jerry,
Maybe my problem is to do with naming a range WorkOrderNum in the workbook. What exactly do you mean by that? I guess I am not sure about "naming a range" Dane. "JLatham" wrote: email me the workbook and I'll see if I can figure out what is not there that needs to be. Make sure that you also have a range named WorkOrderNum in the workbook. email to (remove spaces) HelpFrom @ jlathamsite.com and I'll get it. "Dane" wrote: Hi Jerry I just replied to Dave, as you can see I am still having the same problem after making the corrections to the name of the work sheet. I was also very careful to ensure that there were no leading or trailing spaces. "JLatham" wrote: Dave's suggestion is the same one I would have made. Check the name on the sheet tab and make sure it is exactly the same as you've typed in your code. It is not all that unusual to find that the name on the sheet's tab is either " WorkOrder" or "WorkOrder " instead of actually just "WorkOrder". "Dane" wrote: I too would like to accomplish the same task as Steven Leuck, however I am having trouble. I believe I followed exactly what you indicated but I keep getting a Runtime Error 9. The line listed here is highlighted in yellow in the error message. Worksheets("WorkOrder").Range("WorkOrderNum") = WONum Not sure what I am doing wrong, I would appreciate some help. Could I send you my file? Thank you, Dane Watson "JLatham" wrote: Lets see if this variation of a theme from J.E. McGimpsey won't meet your needs. Use Notepad to create a file to contain your work order number. "Seed" it with the value of the last used work order number. Save the file in the same folder with your work order Excel file. For my example I called the file WONums.txt This code must go into the Workbook's _Open() event handler. To put it there, open your work order workbook and right-click the Excel icon that is immediately to the left of the word [File] in the menu toolbar and choose [View Code] from the pop up list. Copy the code below and paste it into the code window in the VB Editor that shows up. Close the VB Editor, save your file and close it. Next time you open it, the number in WONums.txt will be obtained, incremented and placed into WorkOrderNum. In the code you'll see the line that does that and I've assumed that the worksheet with that named range is named WorkOrder. Change that sheet name as required for your workbook. The last thing the routine does is save the workbook out into the same folder using the work order # created as the filename. Private Sub Workbook_Open() Dim WONumberFile As String Dim WONumbuffer As Integer Dim WONum As Long 'file WONums.txt must be in same folder 'with this Excel file 'find where this file is at and use 'that path to find the WONums.txt file WONumberFile = Left(Me.FullName, _ InStrRev(Me.FullName, Application.PathSeparator)) _ & "WONums.txt" WONumbuffer = FreeFile() 'get the last Work Order Number used Open WONumberFile For Input As #WONumbuffer Input #WONumbuffer, WONum Close #WONumbuffer 'increment last W.O.# WONum = WONum + 1 'place new W.O.# on worksheet '***** change worksheet name as required **** Worksheets("WorkOrder").Range("WorkOrderNum") = WONum 'write the new number back to the WONums.txt file WONumbuffer = FreeFile() Open WONumberFile For Output As #WONumbuffer Print #WONumbuffer, WONum Close #WONumbuffer 'build new name for this file based on 'the assigned W.O.# WONumberFile = Left(WONumberFile, _ InStrRev(WONumberFile, Application.PathSeparator)) _ & Trim(Str(WONum)) & ".xls" 'save the workbook under the new name ThisWorkbook.SaveAs WONumberFile End Sub "Steven Leuck" wrote: I am trying to write a Work Order entry form in Excel to simply a process and automate what seems to be difficult for folks around here. I have a Work Order form that has a number of data fill-in cells with other cells protected. What I would like to do is two fold: 1) Have the spreadsheet automatically place a number in a cell called "WorkOrderNum" that would be one more incrementally than the last file saved. 2) Have the Work Order worksheet automatically use that WorkOrderNum number as the file name when it is saved. The functionality is such that i'm hoping to have the non-computer users here be able to open a single file which will automatically number itself as outlined above and then save with the number for later reference if necessary. Personally, I'd prefer using a single database with all these numbers and worksheet data saved in each row/record. But, knowing how others around here work, I don't think that will be possible. I've poked through the Help system and read several entries here in the forums but don't see exactly what I'm looking for. Any help or suggestions are appreciated. -- Steven Leuck Builders Electric, Inc. |
Automatic File Name Generation
You can name a range by:
Selecting the range Typing the name in the namebox -- to the left of the formula bar (and hit enter) Or by selecting the range insert|name|define Dane wrote: Hi Jerry, Maybe my problem is to do with naming a range WorkOrderNum in the workbook. What exactly do you mean by that? I guess I am not sure about "naming a range" Dane. "JLatham" wrote: email me the workbook and I'll see if I can figure out what is not there that needs to be. Make sure that you also have a range named WorkOrderNum in the workbook. email to (remove spaces) HelpFrom @ jlathamsite.com and I'll get it. "Dane" wrote: Hi Jerry I just replied to Dave, as you can see I am still having the same problem after making the corrections to the name of the work sheet. I was also very careful to ensure that there were no leading or trailing spaces. "JLatham" wrote: Dave's suggestion is the same one I would have made. Check the name on the sheet tab and make sure it is exactly the same as you've typed in your code. It is not all that unusual to find that the name on the sheet's tab is either " WorkOrder" or "WorkOrder " instead of actually just "WorkOrder". "Dane" wrote: I too would like to accomplish the same task as Steven Leuck, however I am having trouble. I believe I followed exactly what you indicated but I keep getting a Runtime Error 9. The line listed here is highlighted in yellow in the error message. Worksheets("WorkOrder").Range("WorkOrderNum") = WONum Not sure what I am doing wrong, I would appreciate some help. Could I send you my file? Thank you, Dane Watson "JLatham" wrote: Lets see if this variation of a theme from J.E. McGimpsey won't meet your needs. Use Notepad to create a file to contain your work order number. "Seed" it with the value of the last used work order number. Save the file in the same folder with your work order Excel file. For my example I called the file WONums.txt This code must go into the Workbook's _Open() event handler. To put it there, open your work order workbook and right-click the Excel icon that is immediately to the left of the word [File] in the menu toolbar and choose [View Code] from the pop up list. Copy the code below and paste it into the code window in the VB Editor that shows up. Close the VB Editor, save your file and close it. Next time you open it, the number in WONums.txt will be obtained, incremented and placed into WorkOrderNum. In the code you'll see the line that does that and I've assumed that the worksheet with that named range is named WorkOrder. Change that sheet name as required for your workbook. The last thing the routine does is save the workbook out into the same folder using the work order # created as the filename. Private Sub Workbook_Open() Dim WONumberFile As String Dim WONumbuffer As Integer Dim WONum As Long 'file WONums.txt must be in same folder 'with this Excel file 'find where this file is at and use 'that path to find the WONums.txt file WONumberFile = Left(Me.FullName, _ InStrRev(Me.FullName, Application.PathSeparator)) _ & "WONums.txt" WONumbuffer = FreeFile() 'get the last Work Order Number used Open WONumberFile For Input As #WONumbuffer Input #WONumbuffer, WONum Close #WONumbuffer 'increment last W.O.# WONum = WONum + 1 'place new W.O.# on worksheet '***** change worksheet name as required **** Worksheets("WorkOrder").Range("WorkOrderNum") = WONum 'write the new number back to the WONums.txt file WONumbuffer = FreeFile() Open WONumberFile For Output As #WONumbuffer Print #WONumbuffer, WONum Close #WONumbuffer 'build new name for this file based on 'the assigned W.O.# WONumberFile = Left(WONumberFile, _ InStrRev(WONumberFile, Application.PathSeparator)) _ & Trim(Str(WONum)) & ".xls" 'save the workbook under the new name ThisWorkbook.SaveAs WONumberFile End Sub "Steven Leuck" wrote: I am trying to write a Work Order entry form in Excel to simply a process and automate what seems to be difficult for folks around here. I have a Work Order form that has a number of data fill-in cells with other cells protected. What I would like to do is two fold: 1) Have the spreadsheet automatically place a number in a cell called "WorkOrderNum" that would be one more incrementally than the last file saved. 2) Have the Work Order worksheet automatically use that WorkOrderNum number as the file name when it is saved. The functionality is such that i'm hoping to have the non-computer users here be able to open a single file which will automatically number itself as outlined above and then save with the number for later reference if necessary. Personally, I'd prefer using a single database with all these numbers and worksheet data saved in each row/record. But, knowing how others around here work, I don't think that will be possible. I've poked through the Help system and read several entries here in the forums but don't see exactly what I'm looking for. Any help or suggestions are appreciated. -- Steven Leuck Builders Electric, Inc. -- Dave Peterson |
Automatic File Name Generation
If you need more help with the instructions Dave Peterson provided, just post
back. What cell on the sheet is it that you want the work order number to appear in? I think at this point it may be a good thing to go ahead and send the workbook to me as an email attachment if you don't have it working after Dave's fix so that I can find anything else wrong with it and provide you with fixes, explanations of the fixes and a post back in here for others to learn from also. A kind of win-win-win situation <g "Dane" wrote: Hi Jerry, Maybe my problem is to do with naming a range WorkOrderNum in the workbook. What exactly do you mean by that? I guess I am not sure about "naming a range" Dane. "JLatham" wrote: email me the workbook and I'll see if I can figure out what is not there that needs to be. Make sure that you also have a range named WorkOrderNum in the workbook. email to (remove spaces) HelpFrom @ jlathamsite.com and I'll get it. "Dane" wrote: Hi Jerry I just replied to Dave, as you can see I am still having the same problem after making the corrections to the name of the work sheet. I was also very careful to ensure that there were no leading or trailing spaces. "JLatham" wrote: Dave's suggestion is the same one I would have made. Check the name on the sheet tab and make sure it is exactly the same as you've typed in your code. It is not all that unusual to find that the name on the sheet's tab is either " WorkOrder" or "WorkOrder " instead of actually just "WorkOrder". "Dane" wrote: I too would like to accomplish the same task as Steven Leuck, however I am having trouble. I believe I followed exactly what you indicated but I keep getting a Runtime Error 9. The line listed here is highlighted in yellow in the error message. Worksheets("WorkOrder").Range("WorkOrderNum") = WONum Not sure what I am doing wrong, I would appreciate some help. Could I send you my file? Thank you, Dane Watson "JLatham" wrote: Lets see if this variation of a theme from J.E. McGimpsey won't meet your needs. Use Notepad to create a file to contain your work order number. "Seed" it with the value of the last used work order number. Save the file in the same folder with your work order Excel file. For my example I called the file WONums.txt This code must go into the Workbook's _Open() event handler. To put it there, open your work order workbook and right-click the Excel icon that is immediately to the left of the word [File] in the menu toolbar and choose [View Code] from the pop up list. Copy the code below and paste it into the code window in the VB Editor that shows up. Close the VB Editor, save your file and close it. Next time you open it, the number in WONums.txt will be obtained, incremented and placed into WorkOrderNum. In the code you'll see the line that does that and I've assumed that the worksheet with that named range is named WorkOrder. Change that sheet name as required for your workbook. The last thing the routine does is save the workbook out into the same folder using the work order # created as the filename. Private Sub Workbook_Open() Dim WONumberFile As String Dim WONumbuffer As Integer Dim WONum As Long 'file WONums.txt must be in same folder 'with this Excel file 'find where this file is at and use 'that path to find the WONums.txt file WONumberFile = Left(Me.FullName, _ InStrRev(Me.FullName, Application.PathSeparator)) _ & "WONums.txt" WONumbuffer = FreeFile() 'get the last Work Order Number used Open WONumberFile For Input As #WONumbuffer Input #WONumbuffer, WONum Close #WONumbuffer 'increment last W.O.# WONum = WONum + 1 'place new W.O.# on worksheet '***** change worksheet name as required **** Worksheets("WorkOrder").Range("WorkOrderNum") = WONum 'write the new number back to the WONums.txt file WONumbuffer = FreeFile() Open WONumberFile For Output As #WONumbuffer Print #WONumbuffer, WONum Close #WONumbuffer 'build new name for this file based on 'the assigned W.O.# WONumberFile = Left(WONumberFile, _ InStrRev(WONumberFile, Application.PathSeparator)) _ & Trim(Str(WONum)) & ".xls" 'save the workbook under the new name ThisWorkbook.SaveAs WONumberFile End Sub "Steven Leuck" wrote: I am trying to write a Work Order entry form in Excel to simply a process and automate what seems to be difficult for folks around here. I have a Work Order form that has a number of data fill-in cells with other cells protected. What I would like to do is two fold: 1) Have the spreadsheet automatically place a number in a cell called "WorkOrderNum" that would be one more incrementally than the last file saved. 2) Have the Work Order worksheet automatically use that WorkOrderNum number as the file name when it is saved. The functionality is such that i'm hoping to have the non-computer users here be able to open a single file which will automatically number itself as outlined above and then save with the number for later reference if necessary. Personally, I'd prefer using a single database with all these numbers and worksheet data saved in each row/record. But, knowing how others around here work, I don't think that will be possible. I've poked through the Help system and read several entries here in the forums but don't see exactly what I'm looking for. Any help or suggestions are appreciated. -- Steven Leuck Builders Electric, Inc. |
Automatic File Name Generation
Thank you Dave - I finally got it - however, see reply to Jerry.
Thanks again, Dane. "Dave Peterson" wrote: You can name a range by: Selecting the range Typing the name in the namebox -- to the left of the formula bar (and hit enter) Or by selecting the range insert|name|define Dane wrote: Hi Jerry, Maybe my problem is to do with naming a range WorkOrderNum in the workbook. What exactly do you mean by that? I guess I am not sure about "naming a range" Dane. "JLatham" wrote: email me the workbook and I'll see if I can figure out what is not there that needs to be. Make sure that you also have a range named WorkOrderNum in the workbook. email to (remove spaces) HelpFrom @ jlathamsite.com and I'll get it. "Dane" wrote: Hi Jerry I just replied to Dave, as you can see I am still having the same problem after making the corrections to the name of the work sheet. I was also very careful to ensure that there were no leading or trailing spaces. "JLatham" wrote: Dave's suggestion is the same one I would have made. Check the name on the sheet tab and make sure it is exactly the same as you've typed in your code. It is not all that unusual to find that the name on the sheet's tab is either " WorkOrder" or "WorkOrder " instead of actually just "WorkOrder". "Dane" wrote: I too would like to accomplish the same task as Steven Leuck, however I am having trouble. I believe I followed exactly what you indicated but I keep getting a Runtime Error 9. The line listed here is highlighted in yellow in the error message. Worksheets("WorkOrder").Range("WorkOrderNum") = WONum Not sure what I am doing wrong, I would appreciate some help. Could I send you my file? Thank you, Dane Watson "JLatham" wrote: Lets see if this variation of a theme from J.E. McGimpsey won't meet your needs. Use Notepad to create a file to contain your work order number. "Seed" it with the value of the last used work order number. Save the file in the same folder with your work order Excel file. For my example I called the file WONums.txt This code must go into the Workbook's _Open() event handler. To put it there, open your work order workbook and right-click the Excel icon that is immediately to the left of the word [File] in the menu toolbar and choose [View Code] from the pop up list. Copy the code below and paste it into the code window in the VB Editor that shows up. Close the VB Editor, save your file and close it. Next time you open it, the number in WONums.txt will be obtained, incremented and placed into WorkOrderNum. In the code you'll see the line that does that and I've assumed that the worksheet with that named range is named WorkOrder. Change that sheet name as required for your workbook. The last thing the routine does is save the workbook out into the same folder using the work order # created as the filename. Private Sub Workbook_Open() Dim WONumberFile As String Dim WONumbuffer As Integer Dim WONum As Long 'file WONums.txt must be in same folder 'with this Excel file 'find where this file is at and use 'that path to find the WONums.txt file WONumberFile = Left(Me.FullName, _ InStrRev(Me.FullName, Application.PathSeparator)) _ & "WONums.txt" WONumbuffer = FreeFile() 'get the last Work Order Number used Open WONumberFile For Input As #WONumbuffer Input #WONumbuffer, WONum Close #WONumbuffer 'increment last W.O.# WONum = WONum + 1 'place new W.O.# on worksheet '***** change worksheet name as required **** Worksheets("WorkOrder").Range("WorkOrderNum") = WONum 'write the new number back to the WONums.txt file WONumbuffer = FreeFile() Open WONumberFile For Output As #WONumbuffer Print #WONumbuffer, WONum Close #WONumbuffer 'build new name for this file based on 'the assigned W.O.# WONumberFile = Left(WONumberFile, _ InStrRev(WONumberFile, Application.PathSeparator)) _ & Trim(Str(WONum)) & ".xls" 'save the workbook under the new name ThisWorkbook.SaveAs WONumberFile End Sub "Steven Leuck" wrote: I am trying to write a Work Order entry form in Excel to simply a process and automate what seems to be difficult for folks around here. I have a Work Order form that has a number of data fill-in cells with other cells protected. What I would like to do is two fold: 1) Have the spreadsheet automatically place a number in a cell called "WorkOrderNum" that would be one more incrementally than the last file saved. 2) Have the Work Order worksheet automatically use that WorkOrderNum number as the file name when it is saved. The functionality is such that i'm hoping to have the non-computer users here be able to open a single file which will automatically number itself as outlined above and then save with the number for later reference if necessary. Personally, I'd prefer using a single database with all these numbers and worksheet data saved in each row/record. But, knowing how others around here work, I don't think that will be possible. I've poked through the Help system and read several entries here in the forums but don't see exactly what I'm looking for. Any help or suggestions are appreciated. -- Steven Leuck Builders Electric, Inc. -- Dave Peterson |
Automatic File Name Generation
Thank you Jerry for your patience with me - I finally got it.
However, there is one problem that occurs, when the Work Order Number is inserted at the cell I have assigned it to, it also inserts the same number in the cell directly below it. By the way, where I think I was going wrong was that I was not "Importing External Data (WONums.txt)" to the appropriate cell as a starting point. Thank you so much again, Dane. "JLatham" wrote: If you need more help with the instructions Dave Peterson provided, just post back. What cell on the sheet is it that you want the work order number to appear in? I think at this point it may be a good thing to go ahead and send the workbook to me as an email attachment if you don't have it working after Dave's fix so that I can find anything else wrong with it and provide you with fixes, explanations of the fixes and a post back in here for others to learn from also. A kind of win-win-win situation <g "Dane" wrote: Hi Jerry, Maybe my problem is to do with naming a range WorkOrderNum in the workbook. What exactly do you mean by that? I guess I am not sure about "naming a range" Dane. "JLatham" wrote: email me the workbook and I'll see if I can figure out what is not there that needs to be. Make sure that you also have a range named WorkOrderNum in the workbook. email to (remove spaces) HelpFrom @ jlathamsite.com and I'll get it. "Dane" wrote: Hi Jerry I just replied to Dave, as you can see I am still having the same problem after making the corrections to the name of the work sheet. I was also very careful to ensure that there were no leading or trailing spaces. "JLatham" wrote: Dave's suggestion is the same one I would have made. Check the name on the sheet tab and make sure it is exactly the same as you've typed in your code. It is not all that unusual to find that the name on the sheet's tab is either " WorkOrder" or "WorkOrder " instead of actually just "WorkOrder". "Dane" wrote: I too would like to accomplish the same task as Steven Leuck, however I am having trouble. I believe I followed exactly what you indicated but I keep getting a Runtime Error 9. The line listed here is highlighted in yellow in the error message. Worksheets("WorkOrder").Range("WorkOrderNum") = WONum Not sure what I am doing wrong, I would appreciate some help. Could I send you my file? Thank you, Dane Watson "JLatham" wrote: Lets see if this variation of a theme from J.E. McGimpsey won't meet your needs. Use Notepad to create a file to contain your work order number. "Seed" it with the value of the last used work order number. Save the file in the same folder with your work order Excel file. For my example I called the file WONums.txt This code must go into the Workbook's _Open() event handler. To put it there, open your work order workbook and right-click the Excel icon that is immediately to the left of the word [File] in the menu toolbar and choose [View Code] from the pop up list. Copy the code below and paste it into the code window in the VB Editor that shows up. Close the VB Editor, save your file and close it. Next time you open it, the number in WONums.txt will be obtained, incremented and placed into WorkOrderNum. In the code you'll see the line that does that and I've assumed that the worksheet with that named range is named WorkOrder. Change that sheet name as required for your workbook. The last thing the routine does is save the workbook out into the same folder using the work order # created as the filename. Private Sub Workbook_Open() Dim WONumberFile As String Dim WONumbuffer As Integer Dim WONum As Long 'file WONums.txt must be in same folder 'with this Excel file 'find where this file is at and use 'that path to find the WONums.txt file WONumberFile = Left(Me.FullName, _ InStrRev(Me.FullName, Application.PathSeparator)) _ & "WONums.txt" WONumbuffer = FreeFile() 'get the last Work Order Number used Open WONumberFile For Input As #WONumbuffer Input #WONumbuffer, WONum Close #WONumbuffer 'increment last W.O.# WONum = WONum + 1 'place new W.O.# on worksheet '***** change worksheet name as required **** Worksheets("WorkOrder").Range("WorkOrderNum") = WONum 'write the new number back to the WONums.txt file WONumbuffer = FreeFile() Open WONumberFile For Output As #WONumbuffer Print #WONumbuffer, WONum Close #WONumbuffer 'build new name for this file based on 'the assigned W.O.# WONumberFile = Left(WONumberFile, _ InStrRev(WONumberFile, Application.PathSeparator)) _ & Trim(Str(WONum)) & ".xls" 'save the workbook under the new name ThisWorkbook.SaveAs WONumberFile End Sub "Steven Leuck" wrote: I am trying to write a Work Order entry form in Excel to simply a process and automate what seems to be difficult for folks around here. I have a Work Order form that has a number of data fill-in cells with other cells protected. What I would like to do is two fold: 1) Have the spreadsheet automatically place a number in a cell called "WorkOrderNum" that would be one more incrementally than the last file saved. 2) Have the Work Order worksheet automatically use that WorkOrderNum number as the file name when it is saved. The functionality is such that i'm hoping to have the non-computer users here be able to open a single file which will automatically number itself as outlined above and then save with the number for later reference if necessary. Personally, I'd prefer using a single database with all these numbers and worksheet data saved in each row/record. But, knowing how others around here work, I don't think that will be possible. I've poked through the Help system and read several entries here in the forums but don't see exactly what I'm looking for. Any help or suggestions are appreciated. -- Steven Leuck Builders Electric, Inc. |
Automatic File Name Generation
Did you select two cells when you created that name?
If you use: Edit|goto and select that name, what gets selected? If you did use two cells, then delete the name and reapply it to just one cell. Dane wrote: Thank you Jerry for your patience with me - I finally got it. However, there is one problem that occurs, when the Work Order Number is inserted at the cell I have assigned it to, it also inserts the same number in the cell directly below it. By the way, where I think I was going wrong was that I was not "Importing External Data (WONums.txt)" to the appropriate cell as a starting point. Thank you so much again, Dane. "JLatham" wrote: If you need more help with the instructions Dave Peterson provided, just post back. What cell on the sheet is it that you want the work order number to appear in? I think at this point it may be a good thing to go ahead and send the workbook to me as an email attachment if you don't have it working after Dave's fix so that I can find anything else wrong with it and provide you with fixes, explanations of the fixes and a post back in here for others to learn from also. A kind of win-win-win situation <g "Dane" wrote: Hi Jerry, Maybe my problem is to do with naming a range WorkOrderNum in the workbook. What exactly do you mean by that? I guess I am not sure about "naming a range" Dane. "JLatham" wrote: email me the workbook and I'll see if I can figure out what is not there that needs to be. Make sure that you also have a range named WorkOrderNum in the workbook. email to (remove spaces) HelpFrom @ jlathamsite.com and I'll get it. "Dane" wrote: Hi Jerry I just replied to Dave, as you can see I am still having the same problem after making the corrections to the name of the work sheet. I was also very careful to ensure that there were no leading or trailing spaces. "JLatham" wrote: Dave's suggestion is the same one I would have made. Check the name on the sheet tab and make sure it is exactly the same as you've typed in your code. It is not all that unusual to find that the name on the sheet's tab is either " WorkOrder" or "WorkOrder " instead of actually just "WorkOrder". "Dane" wrote: I too would like to accomplish the same task as Steven Leuck, however I am having trouble. I believe I followed exactly what you indicated but I keep getting a Runtime Error 9. The line listed here is highlighted in yellow in the error message. Worksheets("WorkOrder").Range("WorkOrderNum") = WONum Not sure what I am doing wrong, I would appreciate some help. Could I send you my file? Thank you, Dane Watson "JLatham" wrote: Lets see if this variation of a theme from J.E. McGimpsey won't meet your needs. Use Notepad to create a file to contain your work order number. "Seed" it with the value of the last used work order number. Save the file in the same folder with your work order Excel file. For my example I called the file WONums.txt This code must go into the Workbook's _Open() event handler. To put it there, open your work order workbook and right-click the Excel icon that is immediately to the left of the word [File] in the menu toolbar and choose [View Code] from the pop up list. Copy the code below and paste it into the code window in the VB Editor that shows up. Close the VB Editor, save your file and close it. Next time you open it, the number in WONums.txt will be obtained, incremented and placed into WorkOrderNum. In the code you'll see the line that does that and I've assumed that the worksheet with that named range is named WorkOrder. Change that sheet name as required for your workbook. The last thing the routine does is save the workbook out into the same folder using the work order # created as the filename. Private Sub Workbook_Open() Dim WONumberFile As String Dim WONumbuffer As Integer Dim WONum As Long 'file WONums.txt must be in same folder 'with this Excel file 'find where this file is at and use 'that path to find the WONums.txt file WONumberFile = Left(Me.FullName, _ InStrRev(Me.FullName, Application.PathSeparator)) _ & "WONums.txt" WONumbuffer = FreeFile() 'get the last Work Order Number used Open WONumberFile For Input As #WONumbuffer Input #WONumbuffer, WONum Close #WONumbuffer 'increment last W.O.# WONum = WONum + 1 'place new W.O.# on worksheet '***** change worksheet name as required **** Worksheets("WorkOrder").Range("WorkOrderNum") = WONum 'write the new number back to the WONums.txt file WONumbuffer = FreeFile() Open WONumberFile For Output As #WONumbuffer Print #WONumbuffer, WONum Close #WONumbuffer 'build new name for this file based on 'the assigned W.O.# WONumberFile = Left(WONumberFile, _ InStrRev(WONumberFile, Application.PathSeparator)) _ & Trim(Str(WONum)) & ".xls" 'save the workbook under the new name ThisWorkbook.SaveAs WONumberFile End Sub "Steven Leuck" wrote: I am trying to write a Work Order entry form in Excel to simply a process and automate what seems to be difficult for folks around here. I have a Work Order form that has a number of data fill-in cells with other cells protected. What I would like to do is two fold: 1) Have the spreadsheet automatically place a number in a cell called "WorkOrderNum" that would be one more incrementally than the last file saved. 2) Have the Work Order worksheet automatically use that WorkOrderNum number as the file name when it is saved. The functionality is such that i'm hoping to have the non-computer users here be able to open a single file which will automatically number itself as outlined above and then save with the number for later reference if necessary. Personally, I'd prefer using a single database with all these numbers and worksheet data saved in each row/record. But, knowing how others around here work, I don't think that will be possible. I've poked through the Help system and read several entries here in the forums but don't see exactly what I'm looking for. Any help or suggestions are appreciated. -- Steven Leuck Builders Electric, Inc. -- Dave Peterson |
Automatic File Name Generation
Thank you once again - you guys are so smart
Much appreciated, Dane. "Dave Peterson" wrote: Did you select two cells when you created that name? If you use: Edit|goto and select that name, what gets selected? If you did use two cells, then delete the name and reapply it to just one cell. Dane wrote: Thank you Jerry for your patience with me - I finally got it. However, there is one problem that occurs, when the Work Order Number is inserted at the cell I have assigned it to, it also inserts the same number in the cell directly below it. By the way, where I think I was going wrong was that I was not "Importing External Data (WONums.txt)" to the appropriate cell as a starting point. Thank you so much again, Dane. "JLatham" wrote: If you need more help with the instructions Dave Peterson provided, just post back. What cell on the sheet is it that you want the work order number to appear in? I think at this point it may be a good thing to go ahead and send the workbook to me as an email attachment if you don't have it working after Dave's fix so that I can find anything else wrong with it and provide you with fixes, explanations of the fixes and a post back in here for others to learn from also. A kind of win-win-win situation <g "Dane" wrote: Hi Jerry, Maybe my problem is to do with naming a range WorkOrderNum in the workbook. What exactly do you mean by that? I guess I am not sure about "naming a range" Dane. "JLatham" wrote: email me the workbook and I'll see if I can figure out what is not there that needs to be. Make sure that you also have a range named WorkOrderNum in the workbook. email to (remove spaces) HelpFrom @ jlathamsite.com and I'll get it. "Dane" wrote: Hi Jerry I just replied to Dave, as you can see I am still having the same problem after making the corrections to the name of the work sheet. I was also very careful to ensure that there were no leading or trailing spaces. "JLatham" wrote: Dave's suggestion is the same one I would have made. Check the name on the sheet tab and make sure it is exactly the same as you've typed in your code. It is not all that unusual to find that the name on the sheet's tab is either " WorkOrder" or "WorkOrder " instead of actually just "WorkOrder". "Dane" wrote: I too would like to accomplish the same task as Steven Leuck, however I am having trouble. I believe I followed exactly what you indicated but I keep getting a Runtime Error 9. The line listed here is highlighted in yellow in the error message. Worksheets("WorkOrder").Range("WorkOrderNum") = WONum Not sure what I am doing wrong, I would appreciate some help. Could I send you my file? Thank you, Dane Watson "JLatham" wrote: Lets see if this variation of a theme from J.E. McGimpsey won't meet your needs. Use Notepad to create a file to contain your work order number. "Seed" it with the value of the last used work order number. Save the file in the same folder with your work order Excel file. For my example I called the file WONums.txt This code must go into the Workbook's _Open() event handler. To put it there, open your work order workbook and right-click the Excel icon that is immediately to the left of the word [File] in the menu toolbar and choose [View Code] from the pop up list. Copy the code below and paste it into the code window in the VB Editor that shows up. Close the VB Editor, save your file and close it. Next time you open it, the number in WONums.txt will be obtained, incremented and placed into WorkOrderNum. In the code you'll see the line that does that and I've assumed that the worksheet with that named range is named WorkOrder. Change that sheet name as required for your workbook. The last thing the routine does is save the workbook out into the same folder using the work order # created as the filename. Private Sub Workbook_Open() Dim WONumberFile As String Dim WONumbuffer As Integer Dim WONum As Long 'file WONums.txt must be in same folder 'with this Excel file 'find where this file is at and use 'that path to find the WONums.txt file WONumberFile = Left(Me.FullName, _ InStrRev(Me.FullName, Application.PathSeparator)) _ & "WONums.txt" WONumbuffer = FreeFile() 'get the last Work Order Number used Open WONumberFile For Input As #WONumbuffer Input #WONumbuffer, WONum Close #WONumbuffer 'increment last W.O.# WONum = WONum + 1 'place new W.O.# on worksheet '***** change worksheet name as required **** Worksheets("WorkOrder").Range("WorkOrderNum") = WONum 'write the new number back to the WONums.txt file WONumbuffer = FreeFile() Open WONumberFile For Output As #WONumbuffer Print #WONumbuffer, WONum Close #WONumbuffer 'build new name for this file based on 'the assigned W.O.# WONumberFile = Left(WONumberFile, _ InStrRev(WONumberFile, Application.PathSeparator)) _ & Trim(Str(WONum)) & ".xls" 'save the workbook under the new name ThisWorkbook.SaveAs WONumberFile End Sub "Steven Leuck" wrote: I am trying to write a Work Order entry form in Excel to simply a process and automate what seems to be difficult for folks around here. I have a Work Order form that has a number of data fill-in cells with other cells protected. What I would like to do is two fold: 1) Have the spreadsheet automatically place a number in a cell called "WorkOrderNum" that would be one more incrementally than the last file saved. 2) Have the Work Order worksheet automatically use that WorkOrderNum number as the file name when it is saved. The functionality is such that i'm hoping to have the non-computer users here be able to open a single file which will automatically number itself as outlined above and then save with the number for later reference if necessary. Personally, I'd prefer using a single database with all these numbers and worksheet data saved in each row/record. But, knowing how others around here work, I don't think that will be possible. I've poked through the Help system and read several entries here in the forums but don't see exactly what I'm looking for. Any help or suggestions are appreciated. -- Steven Leuck Builders Electric, Inc. -- Dave Peterson |
Automatic File Name Generation
Dane, I am glad you have it working now. I think we both owe Dave Peterson a
big thanks for 'covering my back' and making sure you got good, accurate responses in a timely manner. As far as being "so smart", well, I can't speak for Dave, but I can say for myself "we learn from our mistakes, or we are doomed to repeat them". I hate repeating myself <g "Dane" wrote: Thank you once again - you guys are so smart Much appreciated, Dane. "Dave Peterson" wrote: Did you select two cells when you created that name? If you use: Edit|goto and select that name, what gets selected? If you did use two cells, then delete the name and reapply it to just one cell. Dane wrote: Thank you Jerry for your patience with me - I finally got it. However, there is one problem that occurs, when the Work Order Number is inserted at the cell I have assigned it to, it also inserts the same number in the cell directly below it. By the way, where I think I was going wrong was that I was not "Importing External Data (WONums.txt)" to the appropriate cell as a starting point. Thank you so much again, Dane. "JLatham" wrote: If you need more help with the instructions Dave Peterson provided, just post back. What cell on the sheet is it that you want the work order number to appear in? I think at this point it may be a good thing to go ahead and send the workbook to me as an email attachment if you don't have it working after Dave's fix so that I can find anything else wrong with it and provide you with fixes, explanations of the fixes and a post back in here for others to learn from also. A kind of win-win-win situation <g "Dane" wrote: Hi Jerry, Maybe my problem is to do with naming a range WorkOrderNum in the workbook. What exactly do you mean by that? I guess I am not sure about "naming a range" Dane. "JLatham" wrote: email me the workbook and I'll see if I can figure out what is not there that needs to be. Make sure that you also have a range named WorkOrderNum in the workbook. email to (remove spaces) HelpFrom @ jlathamsite.com and I'll get it. "Dane" wrote: Hi Jerry I just replied to Dave, as you can see I am still having the same problem after making the corrections to the name of the work sheet. I was also very careful to ensure that there were no leading or trailing spaces. "JLatham" wrote: Dave's suggestion is the same one I would have made. Check the name on the sheet tab and make sure it is exactly the same as you've typed in your code. It is not all that unusual to find that the name on the sheet's tab is either " WorkOrder" or "WorkOrder " instead of actually just "WorkOrder". "Dane" wrote: I too would like to accomplish the same task as Steven Leuck, however I am having trouble. I believe I followed exactly what you indicated but I keep getting a Runtime Error 9. The line listed here is highlighted in yellow in the error message. Worksheets("WorkOrder").Range("WorkOrderNum") = WONum Not sure what I am doing wrong, I would appreciate some help. Could I send you my file? Thank you, Dane Watson "JLatham" wrote: Lets see if this variation of a theme from J.E. McGimpsey won't meet your needs. Use Notepad to create a file to contain your work order number. "Seed" it with the value of the last used work order number. Save the file in the same folder with your work order Excel file. For my example I called the file WONums.txt This code must go into the Workbook's _Open() event handler. To put it there, open your work order workbook and right-click the Excel icon that is immediately to the left of the word [File] in the menu toolbar and choose [View Code] from the pop up list. Copy the code below and paste it into the code window in the VB Editor that shows up. Close the VB Editor, save your file and close it. Next time you open it, the number in WONums.txt will be obtained, incremented and placed into WorkOrderNum. In the code you'll see the line that does that and I've assumed that the worksheet with that named range is named WorkOrder. Change that sheet name as required for your workbook. The last thing the routine does is save the workbook out into the same folder using the work order # created as the filename. Private Sub Workbook_Open() Dim WONumberFile As String Dim WONumbuffer As Integer Dim WONum As Long 'file WONums.txt must be in same folder 'with this Excel file 'find where this file is at and use 'that path to find the WONums.txt file WONumberFile = Left(Me.FullName, _ InStrRev(Me.FullName, Application.PathSeparator)) _ & "WONums.txt" WONumbuffer = FreeFile() 'get the last Work Order Number used Open WONumberFile For Input As #WONumbuffer Input #WONumbuffer, WONum Close #WONumbuffer 'increment last W.O.# WONum = WONum + 1 'place new W.O.# on worksheet '***** change worksheet name as required **** Worksheets("WorkOrder").Range("WorkOrderNum") = WONum 'write the new number back to the WONums.txt file WONumbuffer = FreeFile() Open WONumberFile For Output As #WONumbuffer Print #WONumbuffer, WONum Close #WONumbuffer 'build new name for this file based on 'the assigned W.O.# WONumberFile = Left(WONumberFile, _ InStrRev(WONumberFile, Application.PathSeparator)) _ & Trim(Str(WONum)) & ".xls" 'save the workbook under the new name ThisWorkbook.SaveAs WONumberFile End Sub "Steven Leuck" wrote: I am trying to write a Work Order entry form in Excel to simply a process and automate what seems to be difficult for folks around here. I have a Work Order form that has a number of data fill-in cells with other cells protected. What I would like to do is two fold: 1) Have the spreadsheet automatically place a number in a cell called "WorkOrderNum" that would be one more incrementally than the last file saved. 2) Have the Work Order worksheet automatically use that WorkOrderNum number as the file name when it is saved. The functionality is such that i'm hoping to have the non-computer users here be able to open a single file which will automatically number itself as outlined above and then save with the number for later reference if necessary. Personally, I'd prefer using a single database with all these numbers and worksheet data saved in each row/record. But, knowing how others around here work, I don't think that will be possible. I've poked through the Help system and read several entries here in the forums but don't see exactly what I'm looking for. Any help or suggestions are appreciated. -- Steven Leuck Builders Electric, Inc. -- Dave Peterson |
| All times are GMT +1. The time now is 07:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com