problem trapping errors using Excel VBA

I have an Excel sheet that uses VBA to open another sheet and read data from it every 5 minutes. The logic is essentially this:

On Error goto whatever
Open the other sheet
return success

whatever:
return failure

If success, reset the timer for 5 minutes.
If failure, reset the timer to try again in 30 seconds.

The reason we might not be able to open the sheet is because it is being written to periodically by another application. If we're trying to read when that's happening, we'll get an error.

This logic works fine if the other sheet we're trying to open is on a local drive. However, it needs to work with the other sheet on a network drive. The problem is, when we get a failure opening the sheet on a network drive, an Excel dialog box pops up displaying the error. (don't get this message when sheet is on a local drive) Our sheet/VBA is essentially locked up until somebody presses "OK" to close that dialog box.

Is there some error handling setting I can change to get around this problem?

I'm running Excel from Office 2007, and XP Pro.

Hi DonChrisite,

What exactly is the error message? Which application is writing to the workbook and how is it doing it?

2 ideas spring to mind: they're quite simple so you may already have thought of and discounted them. Without further info I can't be more specific but maybe someone else can?

Idea 1
You're only reading data from the workbook so can't you just open it as read-only? Wouldn't this bypass the entire issue since you would be able to open the workbook the whole time?

Idea 2
If the workbook is not protected you could use ADO to read the data from it. This would mean you don't have to open the workbook at all.

Regards,
Colin

The error message is a generic network connection error. We can duplicate this in our test environment by simply renaming the file.

Opening the sheet read-only wouldn't help. XLReporter is generating a new Excel sheet every 5 minutes, and overwriting the existing sheet. (the entire file, not just the cell contents) If I'm trying to open the sheet while Windows is overwriting the file, I'll get an error saying it can't open the file.

Using ADO might solve the problem. I would still get an error if I tried to connect while the file was being overwritten, but as long as I can trap that error and handle it within VB, without Excel generating a dialog box, I'll be OK.

Have you tried using this ?
Application.DisplayAlerts = False

If it works, don't forget to put it to True after.
If it doesn't... just forget me ... :)

Opening the sheet read-only wouldn't help
If you do try to open it as read-only, what error number and exact error message do you get?