Hi guys,
I am back with another strange one, this time about file locks.
Here is the situation: I have an Excel file (let's call it Parent) that
contains one sheet (among many) that updates some of its data by linking to
another Excel file (let's call it Child). The Parent file is set to
automatically update the fields that are linked to the Child whenever the
Parent is opened.

Now the problem: Sometimes people loose their network connection while
looking at the child file and the child file is locked. It is then impossible
to open the child file until someone reboots the server to release the lock
on the child file. When the child file has been locked like that, opening the
parent brings up a prompt that allows you to retry the child file or cancel
opening it. You click cancel, because retrying won't help until the server
has been rebooted, and get another prompt which you also cancel out off and
finally you can look at the parent file contents.
The parent file is opened by my update process that runs at night unattended
and when that first prompt comes up, it just sits there and waits until I
notice it the next morning and cancel out of the prompt and the process
continues until it comes across another corrupted child file. I need to find
a way to detect that corrupted child file.

I have tried opening either of files in the code to see if I get an error,
but I dont.
The error number is 0 after I try opening the file using this line of code:

Open FullFileName For Binary Access Read Write Lock Read Write As #f

But when open the workbook like this:

Set xb = xa.Workbooks.Open(fileName, True)

I get 2 prompts if FileName is set to the Parent file and 1 prompt if
FileName is set to the Child file name. All the prompts come from Excel I
think, not the file system.


I am new to VBA, as you know and new to Excel and Office Programming, so I
am in the dark here. The call to Open above tells me if the file is open
already by setting Error.Number to 1, but it does not set Error.Number if the
file can not be opened because of the screwed up lock. I tried running

Open "ChildFileName" For Binary Access Read Write Lock Read Write As #f

And Error.Number is 0 after that code runs but if I call

Set xb = xa.Workbooks.Open("ChildFileName", True)

I can not open the workbook and get that prompt.

Do you know of any way to test for a corrupted file that a work book is
linked too? Or maybe there is a way to detect in code that a workbook has put
up a prompt after it was opened and respond to the prompt?

TIA