How to determine if an excel file is open and who has it open ?

[sebpas]

I'm running an access program that updates 2 excel files with results from sql queries. I would like to know how to determine if either of the excel file is open and if so, who has it open. That way I can inform the user of the access program with a msgbox who's the guilty one.
I've seen it done before, just don't know wich language and methods they used.
Thanks

 

[sebpas]

I've found out how to determine if the file is open but still no clue on how to determine the user. With excel 97, we could use the ~$ file but with excel 2000, there's no such file. It seems to create a temp file in the temp directory with a random name.

Any help or commentary would be appreciated.

 

[Katerine]

Hi sebpas,
Offhand, I can think of two ways to see if a file is open, and one way to see the users. But there are drawbacks to them:

Method 1 (to see if a file is open):
Try a filecopy in VBA. See if it returns an error

This way should work regardless of what your users do, and it should run in both 97 and 2000 (don't know, but I can't think of any reason it shouldn't).

Method 2 (to see if it's open and who is in it):
In the Workbook_Open event, have it write a record to a log database. Store the record's ID field in a module-level variable. Then in the Workbook_Close event, update the record to indicate the user has logged off.

Unfortunately, this way relies on the user to always enable the macros when they open the file. If they don't enable macros, the code won't run.

If you'd like any code examples for either method above, please let me know.

 

[sebpas]

Thanks for the reply Katerine, I was beginning to wonder if anyone would ever reply to me!!

I also thought about the log database or file but there are too many risks involved like not enabling macros, crashing with the excel file open, etc...

As far as to know if the file is locked by someone else, I've found code for that and it works but thanks anyway.

I guess I'll try the VB API forum to see if anyone would know about this...

 

[hinchdog]

another way to determine if file is open...

Set oExcelApp1 = GetObject(, "Excel.Application&quot

If oExcelApp1 Is Nothing Then

If oExcelApp1 Is Nothing Then
MsgBox "Microsoft Excel is not installed on your computer"
End If
Else
MsgBox "Excel is already open - DOH!"
End If

HTH,
Hinchdog
^^
0 ''|__ )
\- _ /
|| ||
|| ||

 

[ratman]

Consider this solution.
This will check to see if a file is open AND if so, tell you the username.

Private Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function NTDomainUserName() As String
Dim strBuffer As String * 255
Dim lngBufferLength As Long
Dim lngRet As Long
Dim strTemp As String
lngBufferLength = 255
strBuffer = String(255, 0)
lngRet = GetUserName(strBuffer, lngBufferLength)
strTemp = UCase(Left(strBuffer, lngBufferLength - 1))
NTDomainUserName = strTemp
End Function

Sub ifopenman()
Dim f As Integer
Dim en As String
Dim ed As String

FullFileName = ("C:\My Excel Files\Inventory Files\ML-Dan.xls&quot
f = FreeFile
On Error Resume Next
Open FullFileName For Binary Access Read Write Lock Read Write As #f
Close #f
If Err.Number <> 0 Then
en = Err.Number
ed = Err.Description
NTDomainUserName
FileAlreadyOpen = True
Err.Clear
MsgBox FullFileName & &quot; Is already open, being used by &quot; & NTDomainUserName & vbCr & vbCr & &quot;Error # &quot; & en & &quot; - &quot; & ed
Else
FileAlreadyOpen = False
End If
'then you could code something like .... If FileAlreadyOpen = True then
End Sub

 

[ide]

Ratman,
but it retrieves the current user's name (my username), not whom it had opened..

 

[sebpas]

Like ide said, you'll get YOUR username, not the one that has the excel file open. There's got to be a way to do this because excel tells you the username when you open it via the GUI. Maybe there's something in the error that tells the username, or something like that. If you guys find anything, give a buzz...
Thanks

 

[ratman]

Although I am still searching for the code one solution would be (if this file is an Excel file) to share it and add an on open routine that would write the username to a cell or text file .....then add an on close routine that would remove the username.

 

[ratman]

Or possibly do something with a comment using a shared files open and close routine I mentioned above that could read the comment.

Range(&quot;A1&quot.AddComment
Range(&quot;A1&quot.Comment.Text Text:=&quot;ratman:&quot; & Chr(10) & &quot;&quot;

 

[sebpas]

The list of users who have the file open is written in the excel file itself, therefore if I know that the file is open by somebody else, I can open in using open <file> as binary and retrieve the username. The only problem is writting code that works everytime with a binary file opened like a text file. I've noticed that there's always a &quot;\ p&quot; before the username so I could look for this string and get the text that comes after. So far, that's the only way I've found for retrieving the username. Maybe there's an API that could do this?
Still searching....

 

[ratman]

Consider something like this....open the file as a shared Excel workbook (I believe read-only will not work).

This routine will write all the user names to the file.
'--------------------------------
Workbooks(&quot;Menu.xls&quot.Activate
un = 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 = &quot;Exclusive&quot;
Case 2
.Cells(row, 3).Value = &quot;Shared&quot;
End Select
Next
End With
'----------------------------------------