• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

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

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

  • 2
  • 2
1 Solution
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?
philipgeckoAuthor Commented:

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.


philipgeckoAuthor Commented:
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
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now