Invalid Sheet Name in Excel 2003

[Lyners]

Hello All,
We have a VB.NET application writen using VS 2003. This application apens an
excel file from a vendor, reads the data and performs whatever functions it
needs. We recently upgraded our sytems to Excel 2003 from Excel 2000. Our
application started erroring out because it was having a problem with opening
the Excel file.

From troubleshooting, the exact problem is in Excel 2003, it does not like
the sheet name (even though the sheet name is "sheet1"). When I open the
Excel file from my vendor using Excel 2003, I get a message box that says
"Errors were detected in 'ExcelName.xls', but Microsoft Office Excel was able
to open the file by making the repairs listed below. Save teh file to make
these reapis pernament."

Renamed invalid sheet name.

This is fine, if opening up Excel on your own, but our application is a
stand alone application that runs everyday, opens a new excel file, performs
its actions, and closes.

With Excel 2003, Our application gets as far as opening, and then errors our
with a message of: "Object reference not set to an instance of an object."
because it cannot open the file due to the error being passed back.

Is there a programmatic way around this error?

Here is my code for opening the Excel File:
Public xlApp As Object
Public xlbook As Object
Public xlsheet As Object

xlApp = CreateObject("Excel.Application")
xlbook = xlApp.Workbooks.Open(OpenFileName) <-- errors here
xlsheet = xlbook.Worksheets.Item(1)

The only solution I can come up with is, uninstall Excel 2003 from my
computer that the application is running on, and re-install Excel 2000. I
would rather try and come up with a solution to the Excel 2003 problem.

Thank you in advance for your help.

Lyners

 

[Armin Zingler]

Renamed invalid sheet name.

Have not much experience in this area but searching for "Renamed in
valid sheet name" finds some links, however not all helpful.

This might give an explanation:
http://groups.google.com/group/microsoft.public.excel.crashesgpfs/browse_frm/thread/86ac8b1cf62d9ccd

Try this

xlbook = xlApp.Workbooks.Open(OpenFileName, corruptload := <value>)

I think adding CorruptLoad will crash in Excel 2000 and earlier. So, I'd
try it without CorrupLoad, first. If it fails, add it. If it fails again
there is another problem.

Here you find what <value> can be:
http://msdn.microsoft.com/en-us/library/aa191840(office.10).aspx
As you don't have a COM automation reference to Excel, you'd probably
have to add the constants (xlNormalLoad, xlRepairFile and xlExtractData)
on your own.


Armin

 

[Lyners]

Thanks Armin,
I searched and searched for an answer but cannot find one. It appears that
if you use Excel 2003 and you have an automated process ,like we do, that if
Excel has a conflist with a file (like Sheet Name), then it will stop the
program. I tried all teh different ways of opening the file, and it will not
get past the eror. The funny thing is, the shet name is "sheet1", which is
the default for Microsoft Excel. it works in 2000, not in 2003. I have not
tried it in 2007.

I'll continue to look, but I think for an immediate fix, we will uninstall
Excel 2003, adn re-install Excell 2000.

Lyners

 

[mario]

Hi,

You could try to use some third party component for working with Excel
file. I can recommend you our GemBox.Spreadsheet .NET component for
reading/writing XLS, XLSX, CSV and HTML files (http://
www.gemboxsoftware.com/GBSpreadsheet.htm).

Here is the list of reasons why it is better to use
GemBox.Spreadsheet: http://www.gemboxsoftware.com/GBSpreadsheet.htm#Automation

You can use GemBox.Spreadsheet free version (http://
www.gemboxsoftware.com/GBSpreadsheetFree.htm) even in your commercial
applications. Free version has 150 rows limit.

--
Mario
GemBox.Spreadsheet for .NET - Easily read and write Excel (XLS, XLSX
or CSV) files
or export to HTML files from your .NET apps. See
http://www.gemboxsoftware.com/GBSpreadsheet.htm

 

[Lyners]

The solution was to uninstall Excel 2003, and install Excel 2000. In Excel
2000 we did not receivei the invalid worksheet name error, thus our program
works now.

Thanks for the help.