Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Hi all

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

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

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

    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
    http://www.google.com/groups?hl=en&lr=lang...eb558d0c&rnum=8

    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
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

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

    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
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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

  9. #9
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  10. #10
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  11. #11
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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

  12. #12
    New Lounger
    Join Date
    Jul 2002
    Location
    Auckland, New Zealand
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    This Worked for me Xl2003 / WinXp


    <pre>Sub TestVBA()
    '// Just change the file to test here
    Const strFileToOpen As String = "Cata.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

    </pre>


  13. #13
    New Lounger
    Join Date
    Mar 2002
    Location
    Groningen, Groningen, Netherlands
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    (URL made clickable by adding <!t><!/t> and <!t><!/t> tags, see <!help=19>Help 19<!/help> - 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. Really great sw!!

    Regards,

    Jos N. van der Kooij

  14. #14
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  15. #15
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thanks for the Information Jos

    I haven

Page 1 of 2 12 LastLast

Posting Permissions

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