Opening a workbook with VBA that has a worksheet with an invalid name

T

Todd Sorensen

Hi -

I am having trouble opening a workbook from VBA that
has a worksheet with an invalid name. I am scanning
shared folders for Excel reports and then opening them to
get some information out of them. However, when I run
into a workbook that has a sheet with an invalid name,
the application crashes with a 1004 error. If I trap the
error, I can continue processing, but the workbook with
the invalid name does not get processed.

I tried to delete the xlveryhiddensheet sheet using the
open event handler, but I never get that far unless the
sheet has a valid name. I need to be able to open the
workbook in spite of the error and fix the name problem
with VBA and then continue on. I am scanning over 17000
workbooks so this can not be done manually.

I am considering a rewrite in .Net to see if I can just
get reference to the workbook with out opening it. The I
might be able to get around this issue.

The code I use to open the workbook is:

' open the report read only
Application.DisplayAlerts = False
On Error GoTo ErrorHandler

Workbooks.Open filename, 0, True
Application.DisplayAlerts = True
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Save workbook without shapes 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
VBA Coding Help for Beginner 0
Code execution is correct but generates Error :The file you aretrying to open is in a different form 3
VBA for Dependents shortcut menu 0
Macro VBA coding, Copying content from one workbook to a new one 0
Compare two different workbook sheet to show the difference in text 0
Delete sheets code error requires object 2

Top