Solved

Auto numbering in Excel template

Posted on 2003-10-31
9
2,331 Views
Last Modified: 2007-11-27
I am creating an order form in Excel and save as a template.  In this template which contain an order number in cell G1 and the initial number is 1000.  I want this number to be automatically increased by 1 every time I open through FILE/NEW. And the template will contain the number (1001...) for the next user.

I think you will come up some great idea to handle it.
0
Comment
Question by:mayleeaburgoon
9 Comments
 
LVL 80

Accepted Solution

by:
byundt earned 32 total points
ID: 9659068
Hi mayleeaburgoon,
I suggest you consider writing the order number to an entry in your registry. This approach was discussed in http://oldlook.experts-exchange.com/Applications/MS_Office/Excel/Q_20783162.html with reference to John Walkenbach's web page discussing serializing http://j-walk.com/ss/excel/tips/tip60.htm
Cheers!

Brad
0
 
LVL 2

Assisted Solution

by:joefunsmith
joefunsmith earned 31 total points
ID: 9660845
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
0
 
LVL 2

Expert Comment

by:joefunsmith
ID: 9660859
by the way, you can still open the template on the server by using the File, New menu if you place something like "shortcut to myTemplate.xlt" in your template folder for each client machine.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 22

Assisted Solution

by:Dreamboat
Dreamboat earned 31 total points
ID: 9663027
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.
:)
0
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 31 total points
ID: 9670101
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.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 10340446
:)

You're doing a great job, Anne.

We appreciate your hard work even if the original question askers do not!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
Outlook Free & Paid Tools
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now