Macro (Word XP)

Topic

New Reply

I use the following macro on a check request form. What is does is give my users (122) the ability to open the check request and have it increment 1 number each time they use it. The macro (lives on the server and they all access the same template) sometimes people will not get a number or they get an error but I see nothing wrong with the vb code.

Sub AutoNew()
‘/Purpose: So that the check number will increment 1 num each time opened.
‘/Created: 8/30/02 12:51 PM
‘/Created By: kstaker
Dim rngtemp As Range

On Error GoTo Err_AutoNew

OrderNew = System.PrivateProfileString(“G:Settingsnew.Txt”, “MacroSettings”, “OrderNew”)

If OrderNew = “” Then
OrderNew = 71000
Else
OrderNew = OrderNew + 1
End If

System.PrivateProfileString(“G:Settingsnew.txt”, “MacroSettings”, “OrderNew”) = OrderNew

ActiveDocument.Bookmarks(“OrderNew”).Range.InsertBefore Format(OrderNew, “00#”)
Set rngtemp = ActiveDocument.Bookmarks(“OrderNew”).Range.Duplicate
rngtemp.Expand unit:=wdWord
ActiveDocument.Bookmarks(“OrderNew”).End = rngtemp.End
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

Exit_AutoNew:

Let me know if you see something that I am missing.

Reply | Quote

Replies

The only thing that comes to mind is that two users try to update G:Settingsnew.txt at the same time (would lead to an error), or that one user tries to get the current value while another is updating it (first user would not get a value). I don’t know how often your 122 users open a check request; if it’s several times a day, conflicts are not unlikely.

What you could do is display the error number and error message in the error handling section of your code; that may give you an idea of what causes the problem.

Reply | Quote

I believe this to be the problem. What I cannot figure is how to fix it. Any ideas besides the access table…. ?

Reply | Quote

Only databases (Access, SQL Server etc.) are built to handle multiple users. A text file, Word document or Excel spreadhsheet (despite the sharing option) are not designed for that. Is there a serious objection against using Access to store the ? That doesn’t mean that the users need to have Access installed – you can use ADO or DAO to retrieve and update the number.

Reply | Quote

Okay, So I have resolved to the fact that I will have to use access but how will I set this up. Any ideas would be great. Then how would I change my macro language to reflect the database instead of the text file number.

Reply | Quote

Have you tried Jan Karel Pieterse’s solution in post 256760? It works with a text file, and uses a loop to keep trying if the text file is in use by someone else.

Reply | Quote

I am not sure how to incorporate it into my code with my bookmarks and my check numbers.

Reply | Quote

I couldn’t get Jan Karels’s code to work, so I modified it, but it may be less “secure” now. Here is your AutoNew with modified code:

Sub AutoNew()
‘/Purpose: So that the check number will increment 1 num each time opened.

Dim rngTemp As Range
Dim sOrderNew As String
Dim lCountTimes As Long
Dim OrderNew As Long

On Error Resume Next

Do
lCountTimes = lCountTimes + 1
Err.Clear
Open “G:SettingsNew.txt” For Input Lock Read Write As #1
If Err = 53 Or Err = 75 Then
MsgBox “SettingsNew.txt not found.”, vbCritical
GoTo Exit_AutoNew
End If
If Err 70 Then
Line Input #1, sOrderNew
OrderNew = CLng(sOrderNew)
OrderNew = OrderNew + 1
Close #1
Open “G:SettingsNew.txt” For Output Lock Read Write As #1
If Err 70 Then
Write #1, OrderNew
End If
End If
Close #1
Loop Until Err = 0 Or lCountTimes = 100

On Error GoTo Err_AutoNew

ActiveDocument.Bookmarks(“OrderNew”).Range.InsertBefore Format(OrderNew, “00#”)
Set rngTemp = ActiveDocument.Bookmarks(“OrderNew”).Range.Duplicate
rngTemp.Expand Unit:=wdWord
ActiveDocument.Bookmarks(“OrderNew”).End = rngTemp.End
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

Exit_AutoNew:
‘ …

Err_AutoNew:
‘ …
End Sub

Reply | Quote

And here is code that uses an Access database. You need to set a reference to the Microsoft ActiveX Data Objects 2.n Library (in the Visual Basic Editor, select Tools | References…) The version number 2.n depends on what is installed.

Sub AutoNew()
‘/Purpose: So that the check number will increment 1 num each time opened.

Dim rngTemp As Range
Dim OrderNew As Long

On Error GoTo Err_AutoNew

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open “Driver={Microsoft Access Driver (*.mdb)};DBQ=G:Settings.mdb;”
rst.CursorType = adOpenKeyset
rst.LockType = adLockPessimistic
rst.Open “SELECT OrderNew FROM tblOrders”, cnn, , , adCmdText
OrderNew = rst!OrderNew + 1
rst!OrderNew = OrderNew
rst.Update
rst.Close
Set rst = Nothing
Set cnn = Nothing

ActiveDocument.Bookmarks(“OrderNew”).Range.InsertBefore Format(OrderNew, “00#”)
Set rngTemp = ActiveDocument.Bookmarks(“OrderNew”).Range.Duplicate
rngTemp.Expand Unit:=wdWord
ActiveDocument.Bookmarks(“OrderNew”).End = rngTemp.End
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

Exit_AutoNew:
‘ …

Err_AutoNew:
‘ …
End Sub

The code expects to find an Access database G:Settings.mdb with a table tblOrders that contains a field OrderNew; you can modify the names of course. I have attached a zipped database with this structure.

Reply | Quote

Jan Karel Pieterse posted a solution for a similar problem in Excel in post 256760. This can be used in Word too; it is not Excel-specific.

Reply | Quote

As a possible solution to the multi-user problem, you could switch to using ADO to access the request counter (e.g., in a one-record Access table). It would be a chunk of overhead, though, that would slow things down a bit.

Reply | Quote

If you are just looking for a unique number for each user and want to stick with the PrivateProfileString multi-user method you could give each user their own .INI file on their C: drive.

Append their next sequential number to their initials or something to make it unique across the group. You’d have to make sure their personal ID was unique.

For instance:

Dory Owen’s 5th check request would be: DO-005
Jon P Smith’s would be: JPS-005

Using Access is best, but this would use what you know and still be unique. Maybe problems would go away if users aren’t trying to share the .INI file.

Reply | Quote