How to detect locked linked xls file ?

[URW]

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 don’t.
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

 

[Jim Cone]

Something to try...
On Error Resume Next
xa.DisplayAlerts = False
Set xb = xa.Workbooks.Open(fileName, True)
If xb is Nothing then
'oops
Else
'Ok
End if
On Error GoTo ErrorHandler
xa.DisplayAlerts = True
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"URW"
wrote in message
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 don’t.
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

 

[URW]

That did it Jim!
I am amazed. I have been searching for a solution for days, my supervisor
googled for a solution to this, but no where did we find this simple line of
code.

I do want to mention though that setting DisplayAlerts to false has global
effects. Once the property is set, alert prompts do not come up for any file
I try to open. So I reset DisplayAlerts to True before exiting the
application, to make sure users with corrupted files get the prompts when
they open their files manually. I want them to be reminded of the problem so
they will get it fixed. It is a minor thing, but I wanted to mention it for
other novice VBA Office Programmers.

My problem is solved, I marked your answer as the solution and I thank you
once again for your help.

I will mention you in my "what I am thankful for this year" list for sure,
Jim.
Have a great Thanksgiving.

Ute

 

[Jim Cone]

You are welcome.
It is also good practice to include DisplayAlerts = True in your error handler.
Sincerely,
Jim Cone


"URW"
wrote in message
That did it Jim!
I am amazed. I have been searching for a solution for days, my supervisor
googled for a solution to this, but no where did we find this simple line of
code.

I do want to mention though that setting DisplayAlerts to false has global
effects. Once the property is set, alert prompts do not come up for any file
I try to open. So I reset DisplayAlerts to True before exiting the
application, to make sure users with corrupted files get the prompts when
they open their files manually. I want them to be reminded of the problem so
they will get it fixed. It is a minor thing, but I wanted to mention it for
other novice VBA Office Programmers.

My problem is solved, I marked your answer as the solution and I thank you
once again for your help.

I will mention you in my "what I am thankful for this year" list for sure,
Jim.
Have a great Thanksgiving.

Ute

 

[URW]

I did remember to do that, but I thank you for the reminder anyway.

Ute