File Reservation - being modified by another user

BigRB · #1 04-15-2004, 07:21 AM

Ok, I cant get this to work, any ideas how i can find out if a file is already in use before my routine runs. The file is on a network and not only do i need to find if its open i want to be able to see who has it open....
Its getting the better of me.

tried so many options and it wont work, can someone help

Thanks

RB

File reservation
"dot.xls" is being modified by BigMB
Open as read only or choose notify to be alerted when it is available....

Mike Rosenblum · #2 04-15-2004, 08:46 AM

Wamphyri provides the following solution as part of his Word FAQ 101 Tutorial: http://www.xtremevbtalk.com/showthread.php?p=650014. It will work for any file, not just a Word document.

herilane · #3 04-15-2004, 10:13 AM

I don't know of any way to find out WHO has the file open, though. The question has been asked before, but no one had a solution.

BigRB · #4 04-15-2004, 10:20 AM

In the general tab under Options you have the user name, if this is populated there must be away to get it..... i just dont know how

Thanks to all who have posted

mark007 · #5 04-16-2004, 02:12 AM

Thinking about this logically Excel must be storing the name of the current user somewhere to be able to display it. I would think it was highly unlikely that this data ws stored on the pc where the file was opened as this would then require it to request the info when the file was opened elsewhere. I therefore think it is likely to be stored within the Excel file itself. I'm at home today so not on a network and unable to test but I would think it may be possible to open the file as text or binary and obtain the username of the person who is in the workbook. You would just need to find the loaction in the file - which unfortunately may take a while! I'l have a play around at work on Monday if I get time. Try opening the file as text in notepad then getting someone else to open the file from their PC. Now open the file in notepad again and see if there are any differences. If so you may be able to work it out. The other issue though is whether the data is encrypted internally or not......

herilane · #6 04-16-2004, 04:49 AM

Well, since I had nothing better to do, I played around with this a little bit

It looks like the name is indeed stored in the Excel file, not encrypted in any way. It's preceded by Chr(0) & Chr(0) - I'll call this Flag1 - and followed by Chr(32) & Chr(32) - call this Flag2. There are a lot of occurrences of Flag1 in the file, but the first occurrence of Flag2 seems to happen just after the name. So if we find Flag2, and find Flag1 just before Flag2, the user name is between the two.

This also works for files that are not in use by another user. In that case it returns the name of the user who last opened the file (or maybe who last modified it, I haven't checked).

Code:

Function LastUser(path 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
j = InStr(1, text, strflag2)
i = InStrRev(text, strFlag1, j) + Len(strFlag1)
LastUser = Mid(text, i, j - i)

End Function

**Timbo** · #7 04-16-2004, 04:52 AM

Quote:

Originally Posted by BigRB

In the general tab under Options you have the user name, if this is populated there must be away to get it.

That is the 'UserName' property of the Application object. However this property refers to the host instance of the object, and thus will always be username of the currently logged-on user on the computer from which the call was made.

Ivan F Moala · #8 04-16-2004, 06:42 AM

Quote:

Originally Posted by herilane

Well, since I had nothing better to do, I played around with this a little bit

It looks like the name is indeed stored in the Excel file, not encrypted in any way. It's preceded by Chr(0) & Chr(0) - I'll call this Flag1 - and followed by Chr(32) & Chr(32) - call this Flag2. There are a lot of occurrences of Flag1 in the file, but the first occurrence of Flag2 seems to happen just after the name. So if we find Flag2, and find Flag1 just before Flag2, the user name is between the two.

This also works for files that are not in use by another user. In that case it returns the name of the user who last opened the file (or maybe who last modified it, I haven't checked).

Code:

Function LastUser(path 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
j = InStr(1, text, strflag2)
i = InStrRev(text, strFlag1, j) + Len(strFlag1)
LastUser = Mid(text, i, j - i)

End Function

Thanks Helen
Following on from your code and integrating into some code from here
http://www.xcelfiles.com/IsFileOpen.html

Then this should work. Have NOT tested this Extensively so........if others
would like to confirm/Test on there system.

Testing done on WinXp / Xl2000/2003/97

Code:

Option Explicit

Sub TestVBA()
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 "File is not open"
    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
j = InStr(1, text, strflag2)
i = InStrRev(text, strFlag1, j) + Len(strFlag1)
LastUser = Mid(text, i, j - i)

End Function

**Timbo** · #9 04-16-2004, 07:46 AM

Nice job herilane & Ivan! I was wondering if that username will be correct before the workbook is saved? Like Ivan, I can't actually test this - no network!

Ivan F Moala · #10 04-16-2004, 07:57 AM

Mark007, got me thinking of this, BUT before I could look into it Helen had already coded it! Nice work Mark & Helen. Got to admit I have been looking for this.
When g8 minds such as MArk, Helen & Timbo get together it makes it easy :-)

**Timbo** · #11 04-16-2004, 08:57 AM

Someone still needs to test this accross a network, where a user has locked the file, but has not yet saved it (even un-intentionally, e.g. autosave) . The file must have been saved previously by another user.

Where's our official tester Mike_R when we need him?

Mike Rosenblum · #12 04-16-2004, 08:59 AM

Ahahaha... I'm the WORST choice for THIS test. LOL

I "work" (yes, work is in quotes, LOL) from home 100% of the time and have no network. Sorry guys!

[Edit: But really impressive though, you guys teamed up to do some great work...]

ReadError · #13 04-16-2004, 09:11 AM

I tested it. Created an Excel file on the network, opened it with another user without saving, and ran the testvba proc. And I got the user that opened it.

Mike Rosenblum · #14 04-16-2004, 09:14 AM

Whoo-hoo!

You guys rock.

Insomniac · #15 04-16-2004, 10:09 AM

Just a couple of comments:

1. Nicely done by all!

2. The InStrRev function does not work in xl97 so changed herilanes code slightly to suit:
from-

Code:

j = InStr(1, text, strflag2)
i = InStrRev(text, strFlag1, j) + Len(strFlag1)
LastUser = Mid(text, i, j - i)

to

Code:

j = InStr(1, text, strflag2)

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 = nam

3. Tested on home network & worked great except when 2nd comp has readonly access to the path the Open path For Binary As #1 did fail??

4. Only a very pedantic observation, but if the user changes their application username after opening the file it is not reflected by the code.

ReadError · #16 04-16-2004, 11:10 AM

Quote:

Originally Posted by Insomniac

3. Tested on home network & worked great except when 2nd comp has readonly access to the path the Open path For Binary As #1 did fail??

Probably because binary mode opens the file in readwrite mode

Insomniac · #17 04-16-2004, 11:25 AM

Quote:

Originally Posted by ReadError

Probably because binary mode opens the file in readwrite mode

Yes, I realised the problems after posting, but decided to leave observation as commented as it will cause code to crash. If the user only has readonly access this is a different issue I think?

mark007 · #18 04-17-2004, 05:13 AM

Glad to see you got it working!

My initial hunch paid off. I suspected it would be but couldn't check while I was at home. I knew excel wrote to the file in some way when you opened it as can be observed by looking at the date last modified in explorer - well there you go!

BigRB · #19 04-19-2004, 05:14 AM

Thanks to all who helped put this together, you saved me a lot of time.....thanks again - BigRB

jpineau · #20 06-24-2004, 01:55 PM

ALL:

This thread has saved us a great deal of time but we have run into the following problem. A previous users name was 15 characters in length. The last users name is 11 characters in length. We are seeing the name for the user that currently has the file open (last user) but we are also seeing the last 4 characters of the previous users name appended to the end of the last users name. We are initializing the strings to "" but no matter what we try we end up with John Pineauauer instead of just John Pineau. What are we missing???

Thanks,

JP