Code execution stops with msg even after using "On Error Resume Next"

[Ashank4vba]

Hello,

I have a bunch of excel files within a folder of which some are corrupted. The corrupted excel files don't open. I get an error message of the format:

<file name here> cannot be accessed. The file may be read-only or you may be trying to access a read-only location. Or , the server the document is stored on may not be respon ding.

I get the above message with retry and cancel buttons.


I am asking this question in the VBA forum because, I am trying to open all the excel files in the folder using VBA and even though I use 'On Error Resume Next' to open these files, I still get the above error and code execution stops temporarily until I press cancel (after which I set it up to proceed to the next file). I don't mind not being able to read these corrupted files (although if anyone has a way I'd be thankful to hear it) but I want my code to run uninterrupted (since I have intended this a batch process to be run during non business hours.

Please help!

 

[GlennUK]

Have you tried setting Application.DisplayAlerts to false?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Ashank4vba]

Thanks Glenn.

However, there seems to be no property called "DisplayAlerts" for the Application object and I get a similar error when I include it in my code.

Any other suggestions?
Thanks.

 

[firefytr]

What program are you calling the VBA code from?

-----------
Regards,
Zack Barresse

 

[Ashank4vba]

From within MS Access.

 

[firefytr]

It's part of the *Excel* application object model.

-----------
Regards,
Zack Barresse

 

[GlennUK]

Yep, it's part of the *Excel* object model ... Excel was mentioned in the original post more than once, whilst Access wasn't mentioned at all. Am not a mind reader.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Ashank4vba]

Thanks Zack and glenn...Didnt realize that the 'Application' object would be different for MS Access and Excel...was happily assuming that VBA objects behaved the same way everywhere..Thanks.

Hmm...is there a way that I can permanently set this DisplayAlerts setting to False (maybe from within a menu in Excel or something)...at least until I parse all these excel files in Access. Thanks.

 

[Ashank4vba]

I'll have to test <my excel application object name here>.Application.DisplayAlerts = False..hopefully this will work..thanks

 

[firefytr]

There isn't really a straight forward way to always have the display alerts to false. When creating an excel instance from access, or from anywhere for that matter, the property is set to true every time so it must be manually changed to False.

Note that in later versions of Excel you'll need to set it back to True if using the Excel instance any farther and you want the setting reverted to it's original state.

-----------
Regards,
Zack Barresse

 

[TomCologne]

What is your setting under

Tools > Options... > General

Error Trapping?

It's probably set to break on all errors, change to "unhandled errors".

TomCologne

 

[firefytr]

Tom,

Just for the record, I generally recommend all errors be handled in code and not handled in any options/properties. I (personally) think that is bad coding practices/habits. Just my 2 cents; stepping off my soap box now.

-----------
Regards,
Zack Barresse

 

[TomCologne]

Zack,

That's why it may be necessary to change that setting in order to prevent breaking;->

TomCologne