Which user has a file open?

[ycim]

Hello all! I am working with VBA from access to manipulate Word documents. So far, so good. However, I am running into some errors when VBA tries to open a word document that is already open. That makes total sense, of course. I want to trap this error, and report back to the user "This file is open on [the persons name]'s machine. Make sure it is closed before you can continue."

In a multi user environment, how do I capture who has the file open, and report that name back to the user? Is this an easy line or two of code, or does it get really complicated (keep in mind, I know virtually nothing about networking).

Thanks

 

[Ed2020]

Taken from

http://www.theofficeexperts.com/for...253f0d1d4260a9e7627f57638787a&p=6716#post6716

I had to amend the ASCII character in the final messagebox to chr(11) (rather than chr(10), as the original version suggests). This may differ dependant on which version of Office you are using. I'm on 2003.

Sub GetOwner(FileName As String)
    Dim tmpOwnerFileName As String
    Dim tmpShort As String
    Dim fso As Object
    Dim fil As Object
    Dim tmpLine As String
     
    Set fso = CreateObject("Scripting.FileSystemObject")
     
     'Making sure if file exists
    If Not fso.FileExists(FileName) Then
        MsgBox "File doesn't exist", vbOKOnly + vbExclamation, "Error"
        Exit Sub
    End If
     
    Set fil = fso.GetFile(FileName)
     
     'Building temporary owner file name
     'I didn't look for another way but this is the basic rule of owner file name
    If Len(fil.Name) <= 10 Then
        tmpShort = fil.Name
    ElseIf Len(fil.Name) <= 12 Then
        tmpShort = Right(fil.Name, 10)
    Else
        tmpShort = Right(fil.Name, Len(fil.Name) - 2)
    End If
     
     'Owner File is located in same directory with Main File
    tmpOwnerFileName = fil.ParentFolder & "\~$" & tmpShort
     
     'If it doesn't exits then it means:
     'File is not opened
     'Or it is not previously saved
    If Not fso.FileExists(tmpOwnerFileName) Then
        MsgBox "File is not opened by another user.", vbOKOnly + vbExclamation, "Information"
        Exit Sub
    End If
     
     'Opening the Owner File and reading the required information
    Open tmpOwnerFileName For Input As 1
    Input #1, tmpLine
    MsgBox "File is being edited by " & Mid(tmpLine, 2, InStr(2, tmpLine, Chr(11)) - 2)
    Close 1
    Set fso = Nothing
End Sub

Ed Metcalfe.

Please do not feed the trolls.....

 

[ycim]

Sweet! I will sit down with your solution this week, and let you know how it goes. It looks pretty easy (thanks for the code).

Talk soon....

 

[Malik641]

The code does not work for me, perhaps my company has some security settings. I've tried numerous Chr() codes. Only some return "
" or less of those characters.

 

[Ed2020]

Malik,

I had to manually check the characters in the string variable to determine what ASCII value they held. The character you are seeing is simply a representation of a non-printable character.

Ed Metcalfe.

Please do not feed the trolls.....