Auto numbering in Excel template

If the registry solution doesn't work for you, I think I would choose to maintain the serial number in a database (Access or SQL) or absent access to a database, like this:

Put the template on the server.  You can have the workbook_open event in the template open a workbook, also on the server, which contains its own workbook_open event.  The 2nd workbook would open, increment a cell, save itself, and close itself.  The first workbook (created from the template would have a link to that cell.  To keep the number static, it would execute a paste value on the linked serial number cell and destroy the link.  

I haven't tried this myself, so there might be a whole in my logic here but offer it only as a possible solution.

joefunsmith

There's a freebie download called MyInvoicing that is already set up to do this.

Smozgur and I created it.

http://www.theofficeexperts.com/downloads.htm#ExcelDownloads

If you can't use it in its entirety, you can steal the code that does the numbering.
:)

Hi,

I replied to a similar posting a few weeks ago:

http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_20742111.html

The below code uses a file ("invoice.txt") stored on a network (h) drive to record the next invoice number to be used.  Upon using the routine the file is updated accordingly so the next time the routine is called the "next" invoice number is returned.  Maybe this will help you?

Public Function Get_Next_Invoice_Number() As Double

  Dim dblReturn                                         As Double
  Dim hndFile                                           As Long
  Dim lngErr_Number                                     As Long
  Dim strErr_Description                                As String
  Dim strInput                                          As String
 
  On Error GoTo Err_Get_Next_Invoice_Number
 
  Const strFilename                                     As String = "h:\invoice.txt"
 
  dblReturn = 0#
  hndFile = FreeFile
 
  Open (strFilename) For Input Shared As hndFile
 
  Input #hndFile, strInput
 
  strInput = Trim$(strInput)
 
  If IsNumeric(strInput) Then
     dblReturn = CDbl(strInput)
  End If
     
  Close #hndFile
 
  hndFile = FreeFile
 
  Open (strFilename) For Output Shared As hndFile
 
  Print #hndFile, dblReturn + 1#
 
Exit_Get_Next_Invoice_Number:

  On Error Resume Next
 
  If hndFile <> 0& Then
     Close #hndFile
  End If
 
  Get_Next_Invoice_Number = dblReturn
 
  Exit Function
 
Err_Get_Next_Invoice_Number:

  lngErr_Number = Err.Number
  strErr_Description = Err.Description
 
  On Error Resume Next
 
  Select Case (lngErr_Number)
 
      Case (0&)
          Resume Next
         
      Case (53&), (62&)                                         ' File Not Found / Input Past End Of File
          Close #hndFile
         
          If Len(Trim$(Dir$(strFilename, vbNormal))) > 0 Then
             SetAttr strFilename, vbNormal
             Kill strFilename
          End If
         
          Open (strFilename) For Output As hndFile
          dblReturn = 1#
          Write #hndFile, dblReturn + 1#
     
      Case (70&)                                                ' Permission Denied
          MsgBox "Another process is using the Invoice Number file - please re-try later.", vbExclamation Or vbOKOnly, Windows(1).Caption
          dblReturn = 0#
         
      Case Else
          MsgBox "Error #" & CStr(lngErr_Number) & vbCrLf & vbLf & strErr_Description, vbExclamation Or vbOKOnly, Windows(1).Caption
          dblReturn = 0#
         
  End Select
 
  Resume Exit_Get_Next_Invoice_Number
 
End Function



BFN,

fp.