Solved

Auto numbering in Excel template

Posted on 2003-10-31
9
2,384 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

752 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