Who has File Open (VBA Excel 2003, Win XP)

Topic

New Reply

Hi all

Is there a way of retrieving the user-name of the person who has a workbook file open on a network ?

Reply | Quote

Replies

I found this snippet of code in the newsgroups:

Dim wbk As Workbook
Dim strFile As String
strFile = “…”
Set wbk = Workbooks.Open(strFile)
If wbk.ReadOnly Then
‘ Workbook is currently open
MsgBox “File is currently opened by ” & wbk.WriteReservedBy
End If

If you only wanted to know who has the workbook open, don’t forget to close it:

wbk.Close SaveChanges:=False

Note: this won’t work if someone has opened a workbook as read-only, because you’ll be able to open it normally.

Reply | Quote

Thanks for the suggestion.

I have tried it but I just get my user name returned by WriteReservedBy !
The information is there somewhere because I do get an screen alert that the “file is currently being modified by “X” (where X is not me) do you want to open the file as read-only ?”
I just can’t seem to get at X
Note the workbook is not a shared workbook.

Do you have any idea how I can get at ie. return X

Thanks for the Help

Reply | Quote

Sorry, no other ideas. Although Excel clearly knows which other user has opened a workbook, this does not seem to be easy to retrieve in VBA.

Reply | Quote

Hi All

Does anyone else have the answer to this one ?

Excel has the answer somwhere I just need to be able to get at it !

Thanks in advance for any assistance

Reply | Quote

Here are some old discussions in the MS Newsgroups archived at Google:

Two threads that discuss this issue.

http://www.google.com/groups?hl=en&lr=lang…ca03aca7&rnum=7%5B/url%5D
http://www.google.com/groups?hl=en&lr=lang…eb558d0c&rnum=8%5B/url%5D

For more ;

http://www.google.com/groups?as_epq=Who Has the File Open&safe=off&ie=UTF-8&oe=UTF-8&as_ugroup=*excel*&lr=lang_en&num=50&hl=en[/url]

Reply | Quote

Especially the userstatus property of a workbook looks promising, but I suspect it is reserved to shared workbooks. From Excel Help:

UserStatus Property
See AlsoApplies ToExampleSpecifics
Returns a 1-based, two-dimensional array that provides information about each user who has the workbook open as a shared list. The first element of the second dimension is the name of the user, the second element is the date and time when the user last opened the workbook, and the third element is a number indicating the type of list (1 indicates exclusive, and 2 indicates shared). Read-only Variant.

Remarks
The UserStatus property doesn’t return information about users who have the specified workbook open as read-only.

Example
This example creates a new workbook and inserts into it information about all users who have the active workbook open as a shared list.

users = ActiveWorkbook.UserStatus
With Workbooks.Add.Sheets(1)
For row = 1 To UBound(users, 1)
.Cells(row, 1) = users(row, 1)
.Cells(row, 2) = users(row, 2)
Select Case users(row, 3)
Case 1
.Cells(row, 3).Value = “Exclusive”
Case 2
.Cells(row, 3).Value = “Shared”
End Select
Next
End With

Reply | Quote

Especially the userstatus property of a workbook looks promising, but I suspect it is reserved to shared workbooks. From Excel Help:

UserStatus Property
See AlsoApplies ToExampleSpecifics
Returns a 1-based, two-dimensional array that provides information about each user who has the workbook open as a shared list. The first element of the second dimension is the name of the user, the second element is the date and time when the user last opened the workbook, and the third element is a number indicating the type of list (1 indicates exclusive, and 2 indicates shared). Read-only Variant.

Remarks
The UserStatus property doesn’t return information about users who have the specified workbook open as read-only.

Example
This example creates a new workbook and inserts into it information about all users who have the active workbook open as a shared list.

users = ActiveWorkbook.UserStatus
With Workbooks.Add.Sheets(1)
For row = 1 To UBound(users, 1)
.Cells(row, 1) = users(row, 1)
.Cells(row, 2) = users(row, 2)
Select Case users(row, 3)
Case 1
.Cells(row, 3).Value = “Exclusive”
Case 2
.Cells(row, 3).Value = “Shared”
End Select
Next
End With

Reply | Quote

Thanks Jan Karel

I have now had a look at those links and it seems that this is a problem that nobody has solved.

Since I know which workbook I am looking at my best bet looks like creating a .txt file containing the user

Reply | Quote

Thanks Jan Karel

I have now had a look at those links and it seems that this is a problem that nobody has solved.

Since I know which workbook I am looking at my best bet looks like creating a .txt file containing the user

Reply | Quote

Here are some old discussions in the MS Newsgroups archived at Google:

Two threads that discuss this issue.

http://www.google.com/groups?hl=en&lr=lang…ca03aca7&rnum=7%5B/url%5D
http://www.google.com/groups?hl=en&lr=lang…eb558d0c&rnum=8%5B/url%5D

For more ;

http://www.google.com/groups?as_epq=Who Has the File Open&safe=off&ie=UTF-8&oe=UTF-8&as_ugroup=*excel*&lr=lang_en&num=50&hl=en[/url]

Reply | Quote

Hi All

Does anyone else have the answer to this one ?

Excel has the answer somwhere I just need to be able to get at it !

Thanks in advance for any assistance

Reply | Quote

Sorry, no other ideas. Although Excel clearly knows which other user has opened a workbook, this does not seem to be easy to retrieve in VBA.

Reply | Quote

Thanks for the suggestion.

I have tried it but I just get my user name returned by WriteReservedBy !
The information is there somewhere because I do get an screen alert that the “file is currently being modified by “X” (where X is not me) do you want to open the file as read-only ?”
I just can’t seem to get at X
Note the workbook is not a shared workbook.

Do you have any idea how I can get at ie. return X

Thanks for the Help

Reply | Quote

I found this snippet of code in the newsgroups:

Dim wbk As Workbook
Dim strFile As String
strFile = “…”
Set wbk = Workbooks.Open(strFile)
If wbk.ReadOnly Then
‘ Workbook is currently open
MsgBox “File is currently opened by ” & wbk.WriteReservedBy
End If

If you only wanted to know who has the workbook open, don’t forget to close it:

wbk.Close SaveChanges:=False

Note: this won’t work if someone has opened a workbook as read-only, because you’ll be able to open it normally.

Reply | Quote

Attached is a zip file of a VB6 project that contains a form and a module. In the module there is a function GetUserNameForFile which I use to find out the user name of the individual who has a file open, if any.

Although it’s a VB6 project, there’s no reason why the module can’t be copied into a VBA project.

Note that it only returns the first user name of any set of users who have the file open but with a little work it could be altered to return an array of user names.

I hope this helps.

Regards,
Kevin Bell

Reply | Quote

Hi Kevin

Thanks for your assistance.

Unfortunately I am not an experienced VB6 programmer and although your code may work I cannot make use of it.

Judging by the Links Jan Karel posted this problem has never been solved, I now have a relatively simple solution for my problem (see My last Post), however I am sure a lot of people would welcome a general solution which could be used in VBA, perhaps if you have the time your code could yield the answer.

Thanks again for your help

Reply | Quote

You are welcome Ralph. For your information please find ax XL 2003 workbook attached that contains the code for finding the user name.

Unzip it, open the workbook, click on the button.

Fill in the name of the server and the name of the file and click the button on the Userform.

Regards,
Kevin Bell

Reply | Quote

You are welcome Ralph. For your information please find ax XL 2003 workbook attached that contains the code for finding the user name.

Unzip it, open the workbook, click on the button.

Fill in the name of the server and the name of the file and click the button on the Userform.

Regards,
Kevin Bell

Reply | Quote

This Worked for me Xl2003 / WinXp

Sub TestVBA()
'// Just change the file to test here
Const strFileToOpen As String = "C:Data.xls"

    If IsFileOpen(strFileToOpen) Then
        MsgBox strFileToOpen & " is already Open" & _
            vbCrLf & "By " & LastUser(strFileToOpen), vbInformation, "File in Use"
        MsgBox ActiveWorkbook.WriteReservedBy
    Else
        MsgBox strFileToOpen & " is not open"
        MsgBox ActiveWorkbook.WriteReservedBy
    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
'// http://www.xcelfiles.com

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 Access Read Write Lock Read Write As hdlFile
    IsFileOpen = False
    Close hdlFile
    Exit Function
FileIsOpen:
    '// Someone has it open!
    IsFileOpen = True
    Close hdlFile
End Function

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 & Mike for the idea
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 strPath For Binary As #1
    text = Space(LOF(1))
    Get 1, , text
Close #1
j = InStr(1, text, strflag2)
i = InStrRev(text, strFlag1, j) + Len(strFlag1)
LastUser = Mid(text, i, j - i)

End Function

Reply | Quote

Thanks for the code.
It does appear to work although I can’t figure out what it does.
The LastUser Function is the only part I need but I am having some difficulty with it.
It does return who has the file open but it writes this over the top of the person who last saved the file. If the last person who saved the file has a user-name 12 characters long and the person who has the file open has a user-name 8 characters long the returned value is the 8 character name concatenated with the last 4 characters of the 12 character name ?

I have tried to figure out what is going on but I can’t see how the code is working, eg text variable does not appear to contain the username when it is displayed in the de###### or when written to a cell ?

Can you supply any information on how this function works ?

Thanks for any assistance.

Reply | Quote

Ok try this amended code


Sub TestVBA()
'// Just change the file to test here
Const strFileToOpen As String = "C:Data.xls"

If IsFileOpen(strFileToOpen) Then
MsgBox strFileToOpen & " is already Open" & _
vbCrLf & "By " & LastUser(strFileToOpen), vbInformation, "File in Use"
Else
MsgBox strFileToOpen & " is not open", vbInformation
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
'// http://www.xcelfiles.com
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 Access Read Write Lock Read Write As hdlFile
IsFileOpen = False
Close hdlFile
Exit Function
FileIsOpen:
'// Someone has it open!
IsFileOpen = True
Close hdlFile
End Function

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


The Username code works on the fact that a workbook opened by a User gets certain info written to it
eg Username. What this code does is to search the file for 2 consecutive spaces chr(32) and works backwards from that possition
until the End of string disignator = 2 vbnullstrings. The character just before this is actually the length of the Username.

Reply | Quote

Thanks Ivan

That works really well

Reply | Quote

Thanks Ivan

That works really well

Reply | Quote

Ok try this amended code


Sub TestVBA()
'// Just change the file to test here
Const strFileToOpen As String = "C:Data.xls"

If IsFileOpen(strFileToOpen) Then
MsgBox strFileToOpen & " is already Open" & _
vbCrLf & "By " & LastUser(strFileToOpen), vbInformation, "File in Use"
Else
MsgBox strFileToOpen & " is not open", vbInformation
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
'// http://www.xcelfiles.com
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 Access Read Write Lock Read Write As hdlFile
IsFileOpen = False
Close hdlFile
Exit Function
FileIsOpen:
'// Someone has it open!
IsFileOpen = True
Close hdlFile
End Function

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


The Username code works on the fact that a workbook opened by a User gets certain info written to it
eg Username. What this code does is to search the file for 2 consecutive spaces chr(32) and works backwards from that possition
until the End of string disignator = 2 vbnullstrings. The character just before this is actually the length of the Username.

Reply | Quote

Thanks for the code.
It does appear to work although I can’t figure out what it does.
The LastUser Function is the only part I need but I am having some difficulty with it.
It does return who has the file open but it writes this over the top of the person who last saved the file. If the last person who saved the file has a user-name 12 characters long and the person who has the file open has a user-name 8 characters long the returned value is the 8 character name concatenated with the last 4 characters of the 12 character name ?

I have tried to figure out what is going on but I can’t see how the code is working, eg text variable does not appear to contain the username when it is displayed in the de###### or when written to a cell ?

Can you supply any information on how this function works ?

Thanks for any assistance.

Reply | Quote

This Worked for me Xl2003 / WinXp

Sub TestVBA()
'// Just change the file to test here
Const strFileToOpen As String = "C:Data.xls"

    If IsFileOpen(strFileToOpen) Then
        MsgBox strFileToOpen & " is already Open" & _
            vbCrLf & "By " & LastUser(strFileToOpen), vbInformation, "File in Use"
        MsgBox ActiveWorkbook.WriteReservedBy
    Else
        MsgBox strFileToOpen & " is not open"
        MsgBox ActiveWorkbook.WriteReservedBy
    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
'// http://www.xcelfiles.com

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 Access Read Write Lock Read Write As hdlFile
    IsFileOpen = False
    Close hdlFile
    Exit Function
FileIsOpen:
    '// Someone has it open!
    IsFileOpen = True
    Close hdlFile
End Function

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 & Mike for the idea
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 strPath For Binary As #1
    text = Space(LOF(1))
    Get 1, , text
Close #1
j = InStr(1, text, strflag2)
i = InStrRev(text, strFlag1, j) + Len(strFlag1)
LastUser = Mid(text, i, j - i)

End Function

Reply | Quote

Hi Kevin

Thanks for your assistance.

Unfortunately I am not an experienced VB6 programmer and although your code may work I cannot make use of it.

Judging by the Links Jan Karel posted this problem has never been solved, I now have a relatively simple solution for my problem (see My last Post), however I am sure a lot of people would welcome a general solution which could be used in VBA, perhaps if you have the time your code could yield the answer.

Thanks again for your help

Reply | Quote

Attached is a zip file of a VB6 project that contains a form and a module. In the module there is a function GetUserNameForFile which I use to find out the user name of the individual who has a file open, if any.

Although it’s a VB6 project, there’s no reason why the module can’t be copied into a VBA project.

Note that it only returns the first user name of any set of users who have the file open but with a little work it could be altered to return an array of user names.

I hope this helps.

Regards,
Kevin Bell

Reply | Quote

(URL made clickable by adding and tags, see Help 19 – Mod)

Hi Ralph,

A bit more in general: If you’re looking for an app that shows files in use on a network you might want to look at WhoHasIt (or WhoHasNT) from http://www.gadgetfactory.com[/url%5D. Really great sw!!

Regards,

Jos N. van der Kooij

Reply | Quote

Thanks for the Information Jos

I haven

Reply | Quote

Thanks for the Information Jos

I haven

Reply | Quote

(URL made clickable by adding and tags, see Help 19 – Mod)

Hi Ralph,

A bit more in general: If you’re looking for an app that shows files in use on a network you might want to look at WhoHasIt (or WhoHasNT) from http://www.gadgetfactory.com[/url%5D. Really great sw!!

Regards,

Jos N. van der Kooij

Reply | Quote