Return User name who has file open

[Juriemagic]

Hi good people!,

I have searched and found this code:

Const strFileToOpen As String = "G:\All Users\Jurie\Test Book 2.xlsx"

    If IsFileOpen(strFileToOpen) Then
        MsgBox strFileToOpen & " is already Open" & _
            vbCrLf & "By " & LastUser(strFileToOpen), vbInformation, "File in Use"
    Else
        Workbooks.Open "G:\All Users\Jurie\Test Book 2.xlsx"
    End If
End Sub

Function IsFileOpen(strFullPathFileName As String) As Boolean
'// VBA version to check if File is Open
'// We can use this for ANY FILE not just Excel!
'// Ivan F Moala
'// [url]http://www.xcelfiles.com[/url]

Dim hdlFile As Long

    '// Error is generated if you try
    '// opening a File for ReadWrite lock >> MUST BE OPEN!
    On Error GoTo FileIsOpen:
    hdlFile = FreeFile
    Open strFullPathFileName For Random Lock Read Write As hdlFile
    IsFileOpen = False
    Close hdlFile
    Exit Function
FileIsOpen:
    '// Someone has it open!
    IsFileOpen = True
    Close hdlFile
End Function

Function LastUser(path As String) As String

Dim text As String
Dim strFlag1 As String, strflag2 As String
Dim i As Integer, j As Integer

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

Open path For Binary As #1
    text = Space(LOF(1))
    Get 1, , text
Close #1

Dim n As Long, nam As String
   
    For n = j - 1 To 1 Step -1
        If Mid(text, n, 1) = Chr(0) Then Exit For
        nam = Mid(text, n, 1) & nam
    Next
   
LastUser = Name



End Function

This code works well, however it fails to supply the current user name of the person who has the file open. It says

"file name" is already open by

I have posted this on Mrexcel.com last week but failed to receive response that really helped me, here:http://www.mrexcel.com/forum/excel-...asic-applications-tell-if-file-read-only.html

I am asking for help with this please, all and any help will be accepted with great humility and respect..Thank you..

 

[vletm]

Something is missing from Your code,
but check/modify next

If just need to know who has the file open then add next from that file

Sub Dialog_WhoIsIn()
    Application.Dialogs(xlDialogFileSharing).Show
End Sub

or longer version

Sub Show_WhoIsIn()
    msg_users = Empty
    Users = ActiveWorkbook.UserStatus
    For Row = 1 To UBound(Users, 1)
        If msg_users <> Empty Then msg_users = msg_users & Chr(13)
        msg_users = msg_users & Users(Row, 1)
    Next
    ans = MsgBox(msg_users, vbInformation, "Users")
End Sub

Of course, the file have to be the ActiveWorkBook.
You can same way open the file You want to check
> then it'll be active > You'll get names ... and so on.
>> Ideas ... questions?

 

[Juriemagic]

unfortunately, the reply gives my username only...it does not tell me who has a specific file open (workbook).
The code as was originally given, actually does not miss any parts, it works 99,99%, meaning this part:

vbCrLf & "By " & LastUser(strFileToOpen), vbInformation

does not retrieve the lock file information...

 

[vletm]

If You're only user with that file then of course it gives only Your name.
If someone else has that file open then You'll get more names.

Do You want 'LastUser' or 'CurrentUsers'?

About 'Your code', where is the beginning?

Const strFileToOpen AsString = "G:\All Users\Jurie\Test Book 2.xlsx"

   If IsFileOpen(strFileToOpen) Then
        MsgBox strFileToOpen & " is already Open" & _
            vbCrLf & "By " & LastUser(strFileToOpen), vbInformation, "File in Use"
   Else
        Workbooks.Open"G:\All Users\Jurie\Test Book 2.xlsx"
   EndIf
EndSub

 

[Juriemagic]

The beginning of the code is as it was given..it start with this:

Const strFileToOpen AsString = "G:\All Users\Jurie\Test Book 2.xlsx"

The part that you gave me, Where should I insert that part into my existing code?..maybe I'm not understanding too well this side...for which I humbly then apologise..
I want the name of the person who has the file open currently..also it's over a network...I really hope you can help me?..Thanx for your time..

 

[vletm]

so far I have know syntax like this; compare to Your code:
Sub NameOfSub() ' Where is this part?
If ... then
...
else
...
endif
EndSub
'My codes':
As I tried to express,
those code should be in those files which You want to check.
That means, those have to run from those files.
... just copy those to any macro-sheet.
Or
You should modify samples as those could run and give result from
'Your "Return User name who has file open" -file'.
Something like next, NOT TESTED AT ALL! ( no files, no Windows )

Sub UNtested_WhoIsIn()
    Workbooks.Open"G:\All Users\Jurie\Test Book 2.xlsx"
    msg_users = Empty
    Users = ActiveWorkbook.UserStatus
    For Row = 1 ToUBound(Users, 1)
        If msg_users <> EmptyThen msg_users = msg_users & Chr(13)
        msg_users = msg_users & Users(Row, 1)
    Next
    ans = MsgBox(msg_users, vbInformation, "Users")
    Close ("Test Book 2.xlsx")
end sub

 

[Juriemagic]

This is the full code:

Sub OpenBook2()
'
' OpenBook2 Macro
'

'
   
Const strFileToOpen As String = "G:\All Users\Jurie\Test Book 2.xlsx"

    If IsFileOpen(strFileToOpen) Then
        MsgBox strFileToOpen & " is already Open" & _
            vbCrLf & "By " & LastUser(strFileToOpen), vbInformation, "File in Use"
    Else
        Workbooks.Open "G:\All Users\Jurie\Test Book 2.xlsx"
    End If
End Sub

Function IsFileOpen(strFullPathFileName As String) As Boolean
'// VBA version to check if File is Open
'// We can use this for ANY FILE not just Excel!
'// Ivan F Moala
'// [url]http://www.xcelfiles.com[/url]

Dim hdlFile As Long

    '// Error is generated if you try
    '// opening a File for ReadWrite lock >> MUST BE OPEN!
    On Error GoTo FileIsOpen:
    hdlFile = FreeFile
    Open strFullPathFileName For Random Lock Read Write As hdlFile
    IsFileOpen = False
    Close hdlFile
    Exit Function
FileIsOpen:
    '// Someone has it open!
    IsFileOpen = True
    Close hdlFile
End Function

Function LastUser(path As String) As String

Dim text As String
Dim strFlag1 As String, strflag2 As String
Dim i As Integer, j As Integer

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

Open path For Binary As #1
    text = Space(LOF(1))
    Get 1, , text
Close #1

Dim n As Long, nam As String
   
    For n = j - 1 To 1 Step -1
        If Mid(text, n, 1) = Chr(0) Then Exit For
        nam = Mid(text, n, 1) & nam
    Next
   
LastUser = Name



End Function

 

[vletm]

The beginning of the code is as it was given..it start with this: - Okay

nam = Mid(text,n,1) & nam
>>>>
LastUser = Name
>>>>
You cannot ever get nam value to LastUser
... also 'Name' and 'path' are many times reserved words
>>>
Did You get an idea?

 

[Juriemagic]

I want to take this opportunity to really thank you for being out there. Thanx for your time spent with this. I got this code from the net as I was searching, and thought to have it tweaked to give current user...but I have reason to believe that this sort of thing cannot be done with Excel 2013...

Thank you once again...have a splendid day!