How to create in VBA for Word 2003 a simple automatic unique quote number

Posted on 2005-05-10
Last Modified: 2010-04-17
Hi there,

I would like to be able to create within Word 2003 a normal document template used for quotations but whenever it is used it will automatically change the quote number to the next number in the sequence ie. 17621, 17622, 17623 etc.  I have very little knowledge regarding vba and would be very grateful if someone could run through with me a simple process that can be applied easily.

Many thanks

Question by:philipgecko
    LVL 11

    Expert Comment

    The Vba will have to index the number based on a save event or an on click event. You have to say more about how you want it to work. Also where is the sequence number to be displayed? In the file name? In a feild on the template?

    Author Comment


    Thanks, yes it will appear something like

    Quotation: 12345
     This will be a field on the template that I guess should be based on a saved event or even every time the template is opened.  Basically what I want is the sales team to use this template and not to have the worry of duplicating the quote number, can this be done..?  if you let me know your email address I can email through to you the working document so that you can have a look at it.



    Author Comment

    I Have some code here which works with Office 2000 and Word 2000, is anyone able to undertand and modify it so that it will work with XP Pro and Word 2003...?

    Sub AutoNew()
      If ActiveDocument.BuiltInDocumentProperties(wdPropertyTitle) = "Technix Quotation" Then
      Link_To_DB = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Jet OLEDB:Database Password=;" & _
                 "Data Source=N:\Act\Database\Quotation.mdb"
      Dim adoCurrent As ADODB.Connection
      Dim adoCurrentRecords As ADODB.Recordset
      Set adoCurrent = New ADODB.Connection
      Set adoCurrentRecords = New ADODB.Recordset
      adoCurrent.Open Link_To_DB
      adoCurrentRecords.Open "SELECT * From Quotation", adoCurrent, adOpenStatic, adLockPessimistic, adCmdText
            If adoCurrentRecords.RecordCount > 0 Then
               Quotation = adoCurrentRecords("NextQuotation")
               adoCurrentRecords("NextQuotation") = 1 + adoCurrentRecords("NextQuotation")
            End If
            Selection.GoTo What:=wdGoToLine, Which:=wdGoToFirst, Count:=19, Name:=""
            Selection.TypeText Text:=CStr(Quotation)
      End If

    End Sub
    LVL 11

    Accepted Solution

    This code requires an Access database to keep track of the Quotation # the table name in the database is Quotation

    first line in the code verifies the the newly created active document is from template named "Technix Quotation"

    Next 9 lines connect to the access database and read the Quatation table for all the previous Quotation #'s
    then if the list is not 0 ie empty then it gets the last quotation # and adds 1 to it and updates the table

    then it steps though the document to a location where it puts the Quotation #

    It appears to me to be missing a declaration or two but not sure without testing

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    pre4 challenge 19 75
    zeroFront challenge 7 58
    Sorting in Excel with Group Headers if the Exist 2 55
    parentbit challenge 3 38
    INTRODUCTION We all know how to code. But at times you simply want to insert a common code block into your existing code and amend it as per your requirements. This tool not only saves you time but also saves you the pain of typing it all out aga…
    This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    746 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