I am using Excel to produce work orders and I need each one to have its own unique number. Much like an invoice or contract number, each time I open the file I would like Excel to generate a new number for that spreadsheet with a value of 1 more than the previous spreadsheet. Does anyone know if this is possible?
There's a question here about knowing what the new work order number is.
How would anybody - you, me or excel; know the number of the previous work order?
For example - if you're creating work order 52. How would we know that work order 51 exists? Do you file them in a particular directory on your PC or is there some sort of central log?
If there's no system like this, it's very hard for anyone to generate a new work order number.
Let me know.
This is frequently asked, and there are several ways to do it.
Depending on your level of experience, the easiest implementaion is the registry, and the most complex in the add-in.
- If numbers are assigned only on your computer,
- You can create a registry key that you increment each time you need a new number.
- You can use an ad-hoc add-in that stores the number on a hidden sheet.
- You can use an ad-hoc workbook or text file on your machine or a network share. For a workbook, open the file, grab the number, increment it and save the file. For a text file, open for read access, get the number, close the file, open for write access, increment the number, close the file.
Last edited by shg; 07-02-2008 at 03:39 PM.
Shg,
Yes, that's what I would like to do. Please tell me how. I have a little experience with Excel but my knowledge is not that broad.
Which one?
The easiest. Although I may have to have multiple users use the same spreadsheet, I would like to figure out how to do it on my own system first.
This goes in a code module:
This goes in the ThisWorkbook module for the workorder template:
Please Login or Register to view this content.
Change the range and formatting as you wish.Please Login or Register to view this content.
Once again, my knowledge about Excel is limited. How do I go about setting up a module?
Open the xls file that contains the workorder template (I'm assuming this is an xls file).
For the first set of code,
1. Copy the code from the post
2. Press Alt+F11 to open the Visual Basic Editor
3. From the menu bar, do Insert > Module
4. Paste the code in the window that opens
Then for the short routine,
1. Copy the code from the post
2. In Excel, right-click on the Excel logo in the upper-left corner and select View Code
3. Paste the code in the window that opens
Then from Excel, do File > Save as, select Template (*.xlt) from the drop-down at the bottom of the window, give it an appropriate name (like WorkOrder) and save in a place you can find later.
When you want to create a new work order, navigate to the directory and double-click the template.
Can't seem to get it to work. Would it be possible for you to attach the file to this thread?
See attached.
GOT IT! Thanks a lot!
You're welcome, glad it worked for you.
Now that everything works, is there a way to "clear the ticker?" For example, if I have run up the numbers and need to resort to a previous number, is there a way to do this? If I try to recreate the file by using an earlier number as the starting point, it seems to always continue from where it left off previously. Is there a file somewhere that is keeping track of the number that I am unable to get wrid of?
Before I used to open the template created and it would generate a new number. I would then save it to an *.xls file and it would keep the number generated. If I would open that *.xls file later, it would have the same number. However, now I have run into a problem where everytime I open the *.xls file it is generating a new number. It is as though the *.xls is acting as if it were my template.
I am not sure if this fact has any effect, but, I have the template located on the server and a link on my desktop to that file. I have been emailing the *.xls file to coworkers and they have been opening it. I was shocked to see when I opened the template one day that the number had increased by 10. Is their opening the *.xls file on their system also increasing the number?
That's what the code does.= presently. Create a new workbook from the template, delete the PO number. Amend the code to thisOriginally Posted by Shmoedoe
Then save as *.xlt, overwriting the existing template. The code will not add a number if A1 is not empty.Please Login or Register to view this content.
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
I open up this file many times. Do I need to add this code every time I want to save the file and not have it update to the next number when opening? Is there an easier way?
If you follow my instructions and create a new template, any susequent orders created from the template will not update. Any made from the previous version will need the code editing.
Personally i don't think that using the workbook open event is the best way. I would use either before save or before print, with a prompt to find out if the number needs updating.
Well that didn't work. I used to be on number 5068 and now its on 76 which totally screws me up. I need to know what to do step by step to start at 5068, to let it add 1 each time I open the template, and, after I save it to a *.xls file, does not add 1 when I reopen the *.xls file. Thanks.
Is there a video to this?
I used the codes as advised but i am having an issue with the numbering and would it be possible to stop the sequence or how best to start from the scratch
Rather than trying track the last ticket number and increment it which has to be stored somewhere reliable between when the system is shut down and restarted , i use the date and time to assign a ticket number which ensures I never get a duplicate ticket number. The ticket number is dependend on the date and time to the 6th decimal place. Depending on the expected minimum length of time between your program assigning work order nos, you can change the granularity up or down by changing the 6.
Please Login or Register to view this content.
Yes.I was shocked to see when I opened the template one day that the number had increased by 10. Is their opening the *.xls file on their system also increasing the number?
Keep the process manual to eliminate your headaches. May not be as elegant but its functional.
bello_e, EssoExplJoe, Logit,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Ben Van Johnson
protontLeah:
Must be a server issue. When I posted my response it was immediately after Shmoedoe's #15 post. I specifically recall because his commentwas what prompted me to respond.Is their opening the *.xls file on their system also increasing the number?
Unusual that three different people would post to the wrong thread ... hmmm
Auto generating work order number
Started by Shmoedoe, 07-02-2008 05:45 AM
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks