VBA - Bypassing Error Report When openning A File

[Lotus Cars]

Hi folks,

Apologies, but this is a bit long winded.

I have an excel file, that when I open the normal way (File, open) will always come up with the following error report

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o>"excel found unreadable content in xxx.xls. Do you want to recover the contents of this workbook?"</o>
<o></o>
<o>So I hit yes, and get</o>
<o></o>
<o>"File error: data may have been lost"</o>
<o></o>
<o>so I hit OK, and get</o>
<o></o>
<o>"Errors were detected in xxx.xls but Microsof Office excel was able to open the file by making the repairs listed below - renamed invalid sheet name"</o>
<o></o>
<o>So I hit close, and the spreadsheet opens fine.</o>
<o></o>
<o>However, when I try and open the same file through a macro</o>
<o></o>
<o>Workbooks.OpenText filename:= "I:\Accts Central\Group Accounts 2011-12\Forecasts\xxx.xls</o>
<o></o>
<o>it just has a strop, and gives me a </o>
<o></o>
<o>"</o><o>runtime error 1004 Method OpenText of object workbooks faild"</o>
<o></o>
<o>message and stops. </o>
<o></o>
<o>I have it wrapped in a Application.DisplayAlerts = False blanket to try ad stop this coming up, but it doesn't.</o>
<o></o>
<o>Is there any way of circumventing the error message?</o>
<o></o>
<o>Any advice / help would be grateful.</o>
<o></o>
<o>Cheers,</o>
<o></o>
<o>Lotus Cars</o>

 

[Sektor]

Workbooks.OpenText filename:= "I:\Accts Central\Group Accounts 2011-12\Forecasts\xxx.xls

Pardon?

 

[Lotus Cars]

Sorry, I meant

Workbooks.Open filename:= "I:\Accts Central\Group Accounts 2011-12\Forecasts\xxx.xls"

Cheers,

Lotus Cars

 

[Sektor]

And does error appear when you open workbook manually?

 

[Lotus Cars]

Yeah, as I tried to describe in my long winded way, openning it manually brings up the error message, but ultimately it will open the file, and the file is OK.

 

[Sektor]

Try to save it under different name. If you have Excel 2007/2010 then it's possible that you have some content inside your Excel file which Excel ignores every time. To watch this, you need to look through the content of your workbook.

 

[Lotus Cars]

Resaved it, and the macro now picks it up. Will just have to remember to do that each time I create the xxx.xls spreadsheet.

Cheers for your help.

 

[Sektor]

Glad that helped ya!