Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    File Access Conflict (VBA/Word & Excel/2003 SP2)

    I am trying to create a log of users launching Word.

    The log will reside on a server accessible by all users.

    The code which populates the log follows: <pre> Open SourceDrive & ULF & "Macro_Users.log" For Append As #1
    Write #1, Environ("username"), Now, SourceDrive & TopSourceFolder
    Close #1
    </pre>


    I am concerned about conflict arising if more than one user attempts to access the log at one time.

    Is anyone aware of a technique for overcoming any conflict?

    Many thanks in advance.
    Regards
    Don

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Access Conflict (VBA/Word & Excel/2003 SP2)

    Something like this:

    <pre> Do
    lCountTimes = lCountTimes + 1
    Err.Clear
    Open sFileName For Input Lock Read Write As #1
    If Err.Number = 53 Or Err.Number = 75 Then
    MsgBox "File not found.", , sAppName
    Exit Sub
    End If
    If Err.Number = 70 Then
    'File in use
    Close #1
    Else
    'Read file, it is free
    Close #1
    DoEvents
    Err.Clear
    'Write to file
    Open sReferencePath & sCounterFileName For Output Lock Read Write As #1
    If Err.Number = 0 Then
    Close #1
    Else
    'File in use
    End If
    End If
    Loop Until Err.Number = 0 Or lCountTimes = 100</pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: File Access Conflict (VBA/Word & Excel/2003 SP2)

    I used to worry about such things but found that the time that it took the file to write the update was so short as to make the likelihood of a problem very small. Ultimately, the worst that can happen is that of two users who both open word at the same instant in time, only one of them goes in the log and the second one gets an error.

    You could add an error handler to ignore this but the chance of it happening is tiny especially if you restart your log file when its speed to open and append becomes significant.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: File Access Conflict (VBA/Word & Excel/2003 SP2)

    Hi Jan Karel

    Thank you ever so much for the help.
    Regards
    Don

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: File Access Conflict (VBA/Word & Excel/2003 SP2)

    Thank you Andrew.
    Regards
    Don

Posting Permissions

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