Checking to see if a paticular file is open or not

[AM003295]

I have a VB6 program that opens an existing Excel workbook fill's in some data and then closes the Excel workbook. My problem is that if two people are using my program at the same time the program could be trying to open the same Excel workbook at the same time and this causes my program problems. Is there a way I can check to see if this paticular Excel sheet is open already by someone else prior to opening it for this user? The paticular Excel workbook is on a common drive that everone can access.

[gibra]

See if this help you.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=443

[AM003295]

This did not work. The file that I need to check, if its open or not, is located on a server and this code only seems to only work if the file is open on the users computer also.

Let me ********, the paticuler file is on a server drive. One user, on computer "A" uses my VB6 program (loaded on there desktop) to open that file on the server, then another user on a different computer, computer "B", opens my program (loaded on there desktop), and if the first user has that paticular file open ( the file located on the server), then his program recognizes that and waits for the other person to finish and the file to close before it then opens the paticular file for itself.
That code only checks to see if it is open on the same computer, I need to know if anyone else has it open on some other computer.


Option Explicit
Option Compare Text

Function IsWbOpen(wbName As String) As Boolean
Dim i As Long
For i = Workbooks.Count To 1 Step -1
If Workbooks(i).Name = wbName Then Exit For
Next
If i <> 0 Then IsWbOpen = True
End Function


'\\ Example
Sub Test()
Dim wb As Workbook, strName As String, strPath As String
strName = "Target Book.xls"
strPath = "C:\Documents and Settings\Rob\Desktop\" '<<== CHANGE TO SUIT
If IsWbOpen(strName) Then
Set wb = Workbooks(strName)
'Make the workbook the Active Workbook
wb.Activate
Else
Set wb = Workbooks.Open(strPath & strName)
End If
End Sub

[gibra]

With this function you can check both on local and server:

Public Function XLS_IsOpen(ByVal sXLSFile As String) As Boolean
    Dim fNum As Integer
    fNum = FreeFile
    On Error GoTo ERR_HANDLER
    Open sXLSFile For Binary Access Write As #fNum
        DoEvents
    Close #fNum
    XLS_IsOpen = False
    
    Exit Function
    
ERR_HANDLER:
    XLS_IsOpen = True
End Function

[AM003295]

Well this latest did not seem to work either. What seems to be happening is that just to open and read is not a problem if someone else has the same file open or not. However if you do a save on it, then it does matter and I'll get an error at that point if that file was open by someone else. Now yes I can captur that error and work accordingly but I need to find out when I first open the file if somone else already has it opened.

If I open the file outside of code (by double clicking on the icon) I get the "File in use..." message, if it is already open by someone else, but if I open it from code I do not.

[AM003295]

Interesting thing happened. When I change the line of code
from this
Open sXLSFile For Binary Access Write As #fNum
to this
Open FileName For Binary Access Read Write Lock Read Write As #1
I then get my error on opening that I expected. Now I can work things out.

Thanks

[Hack]

Did you try simply setting the workbook up as "Shared"?

[AM003295]

No, I don't know anything about "Shared" workbooks but I'll read up on it. That might be a usefull tool in this case. Thanks for the heads up.

[Hack]

Open up the workbook and click: Tools/Share Workbook

Check the box: "Allow changes by more than one user at the same time. This also allows workbook merging"

"OK" yourself back to the workbool.

[AM003295]

This is good but I think when it has changes by more then one it then asks one of the users how to proceed, and my users are not capable of making that kind of decision. (that would take thinking on there part).