Results 1 to 12 of 12

Thread: Macro (Word XP)

  1. #1
    Star Lounger
    Join Date
    Aug 2002
    Location
    Orlando, Florida, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro (Word XP)

    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.InsertB efore Format(OrderNew, "00#")
    Set rngtemp = ActiveDocument.Bookmarks("OrderNew").Range.Duplica te
    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro (Word XP)

    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.

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Macro (Word XP)

    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.

  4. #4
    Star Lounger
    Join Date
    Aug 2002
    Location
    Orlando, Florida, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro (Word XP)

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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro (Word XP)

    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.

  6. #6
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro (Word XP)

    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.
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro (Word XP)

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

  8. #8
    Star Lounger
    Join Date
    Aug 2002
    Location
    Orlando, Florida, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro (Word XP)

    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.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro (Word XP)

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

  10. #10
    Star Lounger
    Join Date
    Aug 2002
    Location
    Orlando, Florida, USA
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro (Word XP)

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

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro (Word XP)

    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.InsertB efore Format(OrderNew, "00#")
    Set rngTemp = ActiveDocument.Bookmarks("OrderNew").Range.Duplica te
    rngTemp.Expand Unit:=wdWord
    ActiveDocument.Bookmarks("OrderNew").End = rngTemp.End
    ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

    Exit_AutoNew:
    ' ...

    Err_AutoNew:
    ' ...
    End Sub

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro (Word XP)

    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.InsertB efore Format(OrderNew, "00#")
    Set rngTemp = ActiveDocument.Bookmarks("OrderNew").Range.Duplica te
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •