Macro error - open file with 'Invalid sheet name' - repair/recover

[Bill]

Another department has a program that exports a report to xls which they
save. I'm helping them by building a macro that has them open this file to
use for a comparison against another file. Problem is, their system exports
the file with a bad sheet name and the code 'Workbooks.Open filename' won't
open it at all and just goes to debug. Anyone know a way around this so it
opens it?

I know they can open it manually, hit ok on the alerts, rename the sheet and
resave. But they aren't really that computer savvy and was hoping not to
risk it. Done it in the past that way and still get calls sometimes from new
users, was hoping to find solution to avoid it this time.

 

[Mike H]

Bill,

I don't understand this. Particularly, how does this other department mange
to save an Excel file with an invalid (Bad) sheet name? excel will reject
illegal sheet names when you try and rename a sheet with an illegal name so
it shouldn't even be possible to create one.

I think we need to see this code that's giving errors, if the workbook can
be opened manually then I'm struggling to understand why it can't be opened
with code.

Mike

 

[Bill]

The program they use doesn't open the file at all so they don't get the
error, it just exports an xls file. We have many programs that tend to do
this and I don't know why their programmers didn't set it up for a valid
sheet name. I tried to open it while recording macro, no extra coding came
up for the errors.

You get the run-time error '1004' - coding is below.

LPFile = Application.GetOpenFilename
Workbooks.Open LPFile