Excel Auto Serial #
12-14-2011, 12:49 PM
#4
So you need an XLT with a startup macro that will read a number from a known, constant location (whether local or network share), increment it, save it back to the file, then insert it into a specific location in your new workbook.
What if you close the workbook without saving it? Is it a requirement that the serial number not have any gaps in sequence?
What if you close the workbook without saving it? Is it a requirement that the serial number not have any gaps in sequence?
12-14-2011, 02:43 PM
#6
Well, keeping the sequence intact is much more difficult since the increment and committing (saving) of the new number has to be delayed until the save. And then you can run into issues like what happens if you open two new workbooks at the same time?
Things sound "simple" when you start until you consider all the possibilities. I'll whip something "simple" up.
Things sound "simple" when you start until you consider all the possibilities. I'll whip something "simple" up.
12-14-2011, 03:33 PM
#7
This is the most simple I could come up with. It will read the current serial number from a text file that you can define to be anywhere using the FILE_NAME constant, then it will increment the number and save it during the save to XLS.
You can open Excel, Hit Alt-F11, double-click ThisWorkbook, and paste this code in. Close the VBA editor and choose File>Save As and choose to save the file as an Excel Template (XLT).
If it doesn't work, remember how much you paid for it. Just kidding. Let me know if it doesn't work and I'll help make it work.
You can open Excel, Hit Alt-F11, double-click ThisWorkbook, and paste this code in. Close the VBA editor and choose File>Save As and choose to save the file as an Excel Template (XLT).
If it doesn't work, remember how much you paid for it. Just kidding. Let me know if it doesn't work and I'll help make it work.
Code:
Private Const FILE_NAME = "c:\sernumber.txt"
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Set ws = ActiveSheet
If IsNumeric(ws.Cells(1, 1)) Then SaveSerial CInt(ws.Cells(1, 1))
End Sub
Private Sub Workbook_Open()
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Cells(1, 1) = ReadSerial
End Sub
Private Function ReadSerial() As Integer
Dim fso As Object
Dim s As Object
Dim text As String
Dim serNumber As Integer
serNumber = 1
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(FILE_NAME) Then
Set s = fso.OpenTextFile(FILE_NAME)
text = s.ReadAll
s.Close
Set s = Nothing
End If
Set fso = Nothing
If IsNumeric(text) Then serNumber = CInt(text)
ReadSerial = serNumber
End Function
Private Sub SaveSerial(newSerial As Integer)
Dim fso As Object
Dim s As Object
newSerial = newSerial + 1
Set fso = CreateObject("Scripting.FileSystemObject")
Set s = fso.OpenTextFile(FILE_NAME, 2, True)
s.Write (CStr(newSerial))
s.Close
Set s = Nothing
Set fso = Nothing
End Sub
Trending Topics
12-14-2011, 05:40 PM
#8
That rocks man^, thanks a bunch!
Humm, would it be possible to make it appear in the header and only +1 after the user hits the Print button?
I tried something different and it worked also, but it did not work in Excel 2003, I have Excel 2010, the user has 2003.
Humm, would it be possible to make it appear in the header and only +1 after the user hits the Print button?
I tried something different and it worked also, but it did not work in Excel 2003, I have Excel 2010, the user has 2003.
12-15-2011, 07:09 AM
#11
Not sure about the header. But to move the updating of the serial # to the print event, I moved the code to the BeforePrint handler and added a flag to only increment the serial number once, so if you print multiple times, it doesn't increment each time.
Code:
Dim alreadySaved As Boolean
Private Const FILE_NAME = "c:\sernumber.txt"
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
Set ws = ActiveSheet
If Not alreadySaved And IsNumeric(ws.Cells(1, 1)) Then
SaveSerial CInt(ws.Cells(1, 1))
alreadySaved = True
End If
End Sub
Private Sub Workbook_Open()
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Cells(1, 1) = ReadSerial
End Sub
Private Function ReadSerial() As Integer
Dim fso As Object
Dim s As Object
Dim text As String
Dim serNumber As Integer
serNumber = 1
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(FILE_NAME) Then
Set s = fso.OpenTextFile(FILE_NAME)
text = s.ReadAll
s.Close
Set s = Nothing
End If
Set fso = Nothing
If IsNumeric(text) Then serNumber = CInt(text)
ReadSerial = serNumber
End Function
Private Sub SaveSerial(newSerial As Integer)
Dim fso As Object
Dim s As Object
newSerial = newSerial + 1
Set fso = CreateObject("Scripting.FileSystemObject")
Set s = fso.OpenTextFile(FILE_NAME, 2, True)
s.Write (CStr(newSerial))
s.Close
Set s = Nothing
Set fso = Nothing
End Sub
12-15-2011, 10:24 AM
#13
Any error messages? Is the number in the text file increasing when you print?
I have made an assumption that the reason you want the number to increment on print is because you want to be able to open a new workbook, type whatever you need, print it, then close it without saving it. Is this the correct workflow? For the sequencing to work properly, you must follow the order of Create>Edit>Print>Save or Close>Create>Edit>Print>Save or Close.
I have made an assumption that the reason you want the number to increment on print is because you want to be able to open a new workbook, type whatever you need, print it, then close it without saving it. Is this the correct workflow? For the sequencing to work properly, you must follow the order of Create>Edit>Print>Save or Close>Create>Edit>Print>Save or Close.
12-15-2011, 11:04 AM
#14
I opened up a new excel workbook, copied the last set of codes you made and put it in the "view code" in the workbook and not module.
Saved as a macro enable workbook. Closed and then open and printed -> nothing.
Closed and open and put in some text then -> printed -> nothing.
Nothing as in no numbers show up on the printed page or the saved and then open sheet.
Saved as a macro enable workbook. Closed and then open and printed -> nothing.
Closed and open and put in some text then -> printed -> nothing.
Nothing as in no numbers show up on the printed page or the saved and then open sheet.
12-15-2011, 02:23 PM
#18
I'm a life-long BASIC/Visual Basic/VB.NET coder. Sadly, I feel my days are numbered. C# is what everyone wants.
If the old joke "Blessed are the geeks, for they will inherit the earth" has any merit, then the Java geeks have claimed it with C# and the Unix geeks have claimed theirs with Android.
If the old joke "Blessed are the geeks, for they will inherit the earth" has any merit, then the Java geeks have claimed it with C# and the Unix geeks have claimed theirs with Android.
12-15-2011, 06:31 PM
#19
Sanest Florida Man
^Damn you iOS!
Thread
Thread Starter
Forum
Replies
Last Post
08_UA7_Gr33k
Member Cars for Sale
13
02-11-2016 02:17 PM
InFaMouSLink
Car Parts for Sale
6
10-27-2015 06:52 PM
detailersdomain
Wash & Wax
3
10-09-2015 10:13 PM
dirleton
2G RDX (2013-2018)
8
09-28-2015 04:48 PM