VBA How to report which User has File Open?

[wageslave101]

I'm trying to work out how I can get which user has a File Open without opening the file itself up.

Thanks,
WageSlave101

 

[Richard Schollar]

Hi

This code is from Ivan F Moala's site (www.xcelfiles.com):

Sub TestFileAlreadyOpen2()
'// We can use this for ANY FILE not just Excel!
Dim strName As String
strName = Application.GetOpenFilename()
Debug.Print strName

    If IsFileAlreadyOpen(strName) Then
        MsgBox strName & " is already Open" & _
            vbCrLf & "By " & LastUser(strName), vbInformation, "File in Use"
    Else
        MsgBox "File is NOT open", vbInformation
    End If
End Sub

Private Function LastUser(strPath As String) As String
'// Code by Helen from http://www.visualbasicforum.com/index.php?s=
'// This routine gets the Username of the File In Use
'// Credit goes to Helen for code & Mark for the idea
'// Insomniac for xl97 inStrRev
'// Amendment 25th June 2004 by IFM
'// : Name changes will show old setting
'// : you need to get the Len of the Name stored just before
'// : the double Padded Nullstrings
Dim strXl As String
Dim strFlag1 As String, strflag2 As String
Dim i As Integer, j As Integer
Dim hdlFile As Long
Dim lNameLen As Byte


strFlag1 = Chr(0) & Chr(0)
strflag2 = Chr(32) & Chr(32)

hdlFile = FreeFile
Open strPath For Binary As #hdlFile
    strXl = Space(LOF(hdlFile))
    Get 1, , strXl
Close #hdlFile

j = InStr(1, strXl, strflag2)

#If Not VBA6 Then
    '// Xl97
    For i = j - 1 To 1 Step -1
        If Mid(strXl, i, 1) = Chr(0) Then Exit For
    Next
    i = i + 1
#Else
    '// Xl2000+
    i = InStrRev(strXl, strFlag1, j) + Len(strFlag1)
#End If

'// IFM
lNameLen = Asc(Mid(strXl, i - 3, 1))
LastUser = Mid(strXl, i, lNameLen)

End Function

 

[hstaubyn]

Thanks for the reply. I have very little idea what is going on here, and unfortunately it doesn't work. I get a run-time error '5' Invalid procedure call or argument. The debug takes me to the line in the code above:

<inamelen =="" asc(mid(strxl,="" i="" -="" 3,="" 1))="">lNameLen = Asc(Mid(strXl, i - 3, 1))

Incidentally, I am using VBA 6.5, but have noticed that the check to see whether it is VBA6 says that it is not. i.e. the line in the code:

[COLOR=#333333]#If Not VBA6 Then[/COLOR]

returns "True"

Other steps in the code and their outputs:

[COLOR=#333333]j = InStr(1, strXl, strflag2)[/COLOR]

gives j=0

[COLOR=#333333]For i = j - 1 To 1 Step -1[/COLOR]
        If Mid(strXl, i, 1) = Chr(0) Then Exit For
Next
i = i + 1

gives i=0

A message box showing the value of strXl shows that:

strXl = PK (followed by 3 weird text boxes)

[Additional inserted code for analysis]

<len(strxl)>Len(strXl)

= 60061

that's about the limit of my diagnostic abilities... Any further help would be much appreciated.</len(strxl)></inamelen>