If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Checking to see if a paticular file is open or not

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.

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

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.

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.

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).