Jeremy Simmons: The Blog

Using a text file to hold sequential numbers

This method is more useful in some situations. The biggest advantage is that the sequential number is no longer tied to a particular machine - it can be stored on a common server, or even a thumb drive. Disadvantages include difficulty in keeping the file from being modified simultaneously by two users, or of the file being more easily deleted or modified. This function will return the next sequential number:

Public Function GetNextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long            
	Const sDEFAULT_PATH As String = "<your path here>"            
	Const sDEFAULT_FNAME As String = "defaultseq.txt"            
	Dim nFileNumber As Long                    
	nFileNumber = FreeFile            
	If sFileName = "" Then sFileName = sDEFAULT_FNAME                
		If InStr(sFileName, Application.PathSeparator) = 0 Then _                       
			sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName            

	If nSeqNumber = -1& Then                    
		If Dir(sFileName) <> "" Then                            
			Open sFileName For Input As nFileNumber                            
			Input #nFileNumber, nSeqNumber
			nSeqNumber = nSeqNumber + 1
			Close nFileNumber                    
		Else                            
			nSeqNumber = 1
		End If            
	End If                        

	On Error GoTo PathError                        
		Open sFileName For Output As nFileNumber                        

	On Error GoTo 0                        
		Print #nFileNumber, nSeqNumber                        
		Close nFileNumber            
		NextSeqNumber = nSeqNumber            
		Exit Function    

	PathError:            
		NextSeqNumber = -1
End Function

If you provide a full path in sFileName, that's where the file will be stored. If not, the file will be stored in whatever default directory you specify. You can set the sequential number by providing a value for nSeqNumber.

Thus, if I'm only using one sequence I can use

Public Sub Workbook_Open()            
	ThisWorkbook.Sheets(1).Range("B2").Value = GetNextSeqNumber    
End Sub

to return the next sequence number. If I'm using multiple sequences, I include the filename (with path, if the text file is not in the default path).

Public Sub NewClientInvoice()            
	This.Workbook.Sheets(1).Range("B2").Value = GetNextSeqNumber("Client1.txt")    
End Sub

And if I want to start a new sequence, beginning at, say, 1001, include that number in the function call. If the client name were in cell B4:

Public Sub SetUpNewClient()
	With ThisWorkbook.Sheets(1)        
		.Range("B2").Value = GetNextSeqNumber(.Range("B4").Value & ".txt", 1001)            
	End With    
End Sub

Original article credit to: http://www.mcgimpsey.com/excel/udfs/sequentialnums.html
Excel is a licensed product of Microsoft Corporation