jeffreywsmith
05-25-2006, 08:45 AM
Well, I had high hopes when I came across the "File Reservation - being modified by another user" thread at: http://www.xtremevbtalk.com/showthread.php?t=160978&page=2&pp=20 and the following code posted there by Ivan F. Moala on June 25, 2004:
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.xtremevbtalk.com/index.php?s=
'// This routine gets the Username of the File In Use
'// Credit goes to Helen for code & Mike for the idea
'// Amendment 25th June 2004
'// : Name changes will show old setting
'// : you need to get the Len of the Name store just before
'// : the double Padded nullstrings
Dim text 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
text = Space(LOF(hdlFile))
Get 1, , text
Close #hdlFile
j = InStr(1, text, strflag2)
i = InStrRev(text, strFlag1, j) + Len(strFlag1)
lNameLen = Asc(Mid(text, i - 3, 1))
LastUser = Mid(text, i, lNameLen)
End Function
... but this has been my experience in attempting to get it to work with a file saved with a password required to open it (I had the same experience using Excel 2K, Excel XP and Excel 2003):
1) Running the above code against the password-protected file, the code fails with "Run-time error '6': Overflow" with the code halted on the statement (in the "LastUser") procedure:
j = InStr(1, text, strflag2)
2) I don't know what happened to it, but when I first viewed the original thread yesterday, there was a post (in March 2006, I think), by member "Birdy", where he/she briefly discussed an unidentified error encountered with that same line of code, and then subsequently replaced it with this:
j = 2604
(Birdy's post is no longer visible in the original thread (??) - not sure what's up with that?)
Anyway, I tried Birdy's suggestion and this time, I did not get the Overflow error but the "File in Use" message I got was incomplete: it identified the path and filename appropriately but this was the text of it: "path\filename is already Open by /" ... and no user was identified.
If it sheds any light, I was doing these tests from 2 different User sessions on a laptop, both trying to access the same password-protected file on a desktop over a home network connection.
Can anyone suggest modifications to the above or otherwise provide the syntax to use in the Open statements in the "LastUser" (and "IsFileOpen") procedure(s) to open a password-protected file in order to return the first user's username ... or if it is even possible?
I appreciate any insights.
Jeff
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.xtremevbtalk.com/index.php?s=
'// This routine gets the Username of the File In Use
'// Credit goes to Helen for code & Mike for the idea
'// Amendment 25th June 2004
'// : Name changes will show old setting
'// : you need to get the Len of the Name store just before
'// : the double Padded nullstrings
Dim text 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
text = Space(LOF(hdlFile))
Get 1, , text
Close #hdlFile
j = InStr(1, text, strflag2)
i = InStrRev(text, strFlag1, j) + Len(strFlag1)
lNameLen = Asc(Mid(text, i - 3, 1))
LastUser = Mid(text, i, lNameLen)
End Function
... but this has been my experience in attempting to get it to work with a file saved with a password required to open it (I had the same experience using Excel 2K, Excel XP and Excel 2003):
1) Running the above code against the password-protected file, the code fails with "Run-time error '6': Overflow" with the code halted on the statement (in the "LastUser") procedure:
j = InStr(1, text, strflag2)
2) I don't know what happened to it, but when I first viewed the original thread yesterday, there was a post (in March 2006, I think), by member "Birdy", where he/she briefly discussed an unidentified error encountered with that same line of code, and then subsequently replaced it with this:
j = 2604
(Birdy's post is no longer visible in the original thread (??) - not sure what's up with that?)
Anyway, I tried Birdy's suggestion and this time, I did not get the Overflow error but the "File in Use" message I got was incomplete: it identified the path and filename appropriately but this was the text of it: "path\filename is already Open by /" ... and no user was identified.
If it sheds any light, I was doing these tests from 2 different User sessions on a laptop, both trying to access the same password-protected file on a desktop over a home network connection.
Can anyone suggest modifications to the above or otherwise provide the syntax to use in the Open statements in the "LastUser" (and "IsFileOpen") procedure(s) to open a password-protected file in order to return the first user's username ... or if it is even possible?
I appreciate any insights.
Jeff