Problem using VBA to create sequentially numbered documents in Word

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SLind - 15 Nov 2005 12:33 GMT

Hi,
I am using a certain Autonew VBA program to automatically generate a new
invoice number every time a certain word document is opened. Everytime I use
it, it works great UNLESS I switch users on my computer, it which case I get
the error message "Method 'PrivateProfileString' of object 'System' failed"
Any help is greatly appreciated. Here is the code, which was borrowed from
Doug Robbin's article "Creating sequentially numbered documents (such as
invoices)" from Microsoft MVP.
thanks again,

Steve

Private Sub Document_Open()

Order = System.PrivateProfileString("C:\Settings.Txt", _
"MacroSettings", "Order")

If Order = "" Then
Order = 1
Else
Order = Order + 1
End If

System.PrivateProfileString("C:\Settings.Txt", "MacroSettings", _
"Order") = Order

ActiveDocument.Bookmarks("Order").Range.InsertBefore Format(Order, "00#")
ActiveDocument.SaveAs FileName:="order" & Format(Order, "#")

End Sub

Reply to this Message

Jonathan West - 15 Nov 2005 12:57 GMT

Hi Steve

I have had occasional problems of this kind with the PrivateProfileString
property. There seems to be no rhyme or reason to the problems, and as a
result, I no longer use it to read & write INI files. Instead, I use the
code created by Karl Peterson that allows for a much wider range of
operations on INI files. Take a look here

http://vb.mvps.org/samples/project.asp?id=kpini

Signature

Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

> Hi,
> I am using a certain Autonew VBA program to automatically generate a new
[quoted text clipped - 29 lines]
>
> End Sub

Reply to this Message

SLind - 17 Nov 2005 00:33 GMT

Thanks for your help, but I'm new to VBA programming and lost looking at the
link you provided.
Let me ask if the following would be possible:
Have two Word documents. One would be a document that poeple would open and
edit, etc. The second document would have only one function... maintain an up-
to-date invoice number. A Macro would be created in the first document to
cross reference to the second document's invoice number.
Here's the problem I would run into... is it possible to increase the invoice
number (new invoice number = old invoice number +1) in the second document
without ever opening it. Could a macro in document 1 update document 2? Any
help with sample VBA code would be greatly appreciated.
thanks
Steve

>Hi Steve
>
[quoted text clipped - 11 lines]
>>
>> End Sub

Reply to this Message

Jezebel - 17 Nov 2005 00:48 GMT

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

Reply to this Message

SLind - 17 Nov 2005 05:54 GMT

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

>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
[quoted text clipped - 10 lines]
>>>>
>>>> End Sub

Reply to this Message

Jonathan West - 17 Nov 2005 11:42 GMT

if you have multiple users and a single common number sequence, by
definition you are going to need the source of those numbers to be in a
location which is available to all the users. And also, you are going to
need to be able to do it in a way that can cope with two simultaneous
requests from different people.

If you are new to programming, I suspect that this is going to be a bit
beyond you - you have picked a multi-access multi-user problem for what
appears to be one of your first programming projects.

You need a database for this. Admittedly a very small one, but a database
nevertheless. I'm not a database programmer, so at this point I'm going to
drop out of this conversation.

Signature

Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

> Thanks for your reply.
> The problem I am running into is:
[quoted text clipped - 27 lines]
>>>>>
>>>>> End Sub

Reply to this Message

Doug Robbins - Word MVP - 17 Nov 2005 20:44 GMT

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.

Signature

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

> Thanks for your reply.
> The problem I am running into is:
[quoted text clipped - 27 lines]
>>>>>
>>>>> End Sub

Reply to this Message