Re: Problem using VBA to create sequentially numbered documents in Word

Tech-Archive recommends: Fix windows errors by optimizing your registry

The following code in an autonew macro in the template from which the
documents are created will add 1 to the last number used in an Access
database, and insert that number into a document variable in the document

Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim LastNumber As Long, NewNumber As Long

vConnection.ConnectionString = _
"data source=c:\AQS\AQS.mdb;" & _ 'Use your own database
"Provider=Microsoft.Jet.OLEDB.4.0;"

vConnection.Open

vRecordSet.Open "tblNumbers", vConnection, adOpenKeyset, adLockOptimistic

vRecordSet.MoveLast
LastNumber = vRecordSet!Number
NewNumber = LastNumber + 1
vRecordSet.AddNew
vRecordSet!Number = NewNumber
vRecordSet.Update
ActiveDocument.Variables("varNumber").Value = NewNumber
ActiveDocument.Fields.Update
vRecordSet.Close
vConnection.Close

Set vRecordSet = Nothing
Set vConnection = Nothing

It is based on the datasource having a table called tblNumbers which
contains a number field with the fieldname of number. The Template must
have a { DOCVARIABLE varNumber } field in it to display the number.

In the template, you need to set a reference to the Microsoft ActiveX Data
Objects #.# Library under Tools>References in the Visual Basic Editor.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"SLind via OfficeKB.com" <u15764@uwe> wrote in message
news:577b594572876@xxxxxx
> Thanks for your reply.
> The problem I am running into is:
> I need to create a new sequential number everytime one of 4-5 other
> documents
> is opened. In other words, if I opened document B, it might have an
> invoice
> number of, say 10027. If someone else opens document D a second later, it
> would need to have an invoice number of 10028. The use of a
> "privateprofilestring" does not appear to work when there are multiple
> users
> on multiple computers in a network.
> Any help is greatly appreciated.
> thanks,
> Steve
> dfwtxsteve (at) aol.com
>
> Jezebel wrote:
>>You can't update another document without opening it. A macro in document
>>1
>>could update document2, but it would have to open it to do so. But using a
>>second document just to store your number is unnecessary.
>>
>>A simpler method would be to store a document variable in the template
>>containing the code (or in normal.dot). Or store the variable in a text
>>file, using ordinary input/print statements. Or even in the registry, for
>>that matter.
>>
>>> Thanks for your help, but I'm new to VBA programming and lost looking at
>>> the
>>[quoted text clipped - 30 lines]
>>>>>
>>>>> End Sub
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/word-programming/200511/1


.

Relevant Pages

  • template with auto fields
    ... put DocProperty Fields in your template header or footer. ... The AutoNew Macro code is: ...
    (microsoft.public.word.pagelayout)
  • Re: Can I create sequentially numbered documents on a server?
    ... "Jay Freedman" wrote: ... with Sub AutoNew() at the beginning and with End Sub at the ... This will cause the Purchase Order template -- and any other templates you ... You need to copy the AutoNew macro code from the article and put it ...
    (microsoft.public.word.docmanagement)
  • Re: User form and macro to run on open
    ... of a stretch for a vba beginner. ... A simpler solution is to use an ask field and an includetext field in the ... You will need an autonew macro in the template to fire the ASK field when a ... I did the Autonew macro bit and it works perfectly. ...
    (microsoft.public.word.vba.general)
  • Re: Follow-Up ... Creating Sequential Part Deux
    ... access problem, if the second, then we are facing a network rights problem. ... > template resides on the company network. ... > I have inserted an AutoNew macro for autonumbering (see ... > If ReqNo = "" Then ...
    (microsoft.public.word.vba.general)
  • Follow-Up ... Creating Sequential Part Deux
    ... Have attached my posting from yesterday, which Doug ... Robbins responded to.... ... template resides on the company network. ... I have inserted an AutoNew macro for autonumbering (see ...
    (microsoft.public.word.vba.general)