Solved

Auto numbering in Excel template

Posted on 2003-10-31
9
2,361 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 81

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
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…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

776 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