asked on

Problem reading some excel files

Hello,

I am reading excel files as follow;

Dim oXL As excel.Application
Dim oBook As excel.Workbook
Dim oSheet As excel.Worksheet

'create app
oXL = CreateObject("Excel.Application")
oXL.DisplayAlerts = False

'creat workbook
oBook = oXL.Workbooks.Open("C:\070206_shb_rh_s05_activité_CG100.xls", Type.Missing, True, Type.Missing, Type.Missing, Type.Missing, False, Type.Missing, Type.Missing, Type.Missing, False, Type.Missing, Type.Missing, Type.Missing, Type.Missing)

'selecting sheets/renaming sheets
oBook.Worksheets("BALANCE AGEE").Select()
oSheet = oBook.Worksheets("BALANCE AGEE")

Dim str As String = oSheet.Range("B6").Value

For some files its fine. But for others, I get an error at this line:
oBook.Worksheets("BALANCE AGEE").Select()

For the files I am having problems, I get a warning stating that the workbook contains external links. It asks whether it shoold update or ignore. No matter my choice i get:

[COMException (0x8002000b): Index non valide. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))]
Microsoft.Office.Interop.Excel.Sheets.get__Default(Object Index) +0
test2.Page_Load(Object sender, EventArgs e) in C:\Documents and Settings\BABA\Mes documents\Visual Studio 2005\sifca\test2.aspx.vb:27
System.Web.UI.Control.OnLoad(EventArgs e) +88
System.Web.UI.Control.LoadRecursive() +74
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3035

Can someone help me on this?

Ryan Chong

think line:

oBook.Worksheets("BALANCE AGEE").Select()

can be omitted in your case here, what if you removed/remarked this line?

bkourouma

ASKER

ryancys,
I still get the error.

Also I need to make sure that annoying warning does not come up.

Thanks.

Ryan Chong

Can you make sure every excel documents got a worksheet called as "BALANCE AGEE" ?

bkourouma

ASKER

Yes, it does. The document I am reading does have ."BALANCE AGEE"

As matter of fact, here is the actual doc http://www.allianceconsultants.net/070206_shb_rh_s05_activité_CG100.xls

Thanks for your time.

bkourouma

ASKER

Thanks, the problem was indeed the name in the sheet contained empty string as in ."BALANCE AGEE ".
Is there a way to rename the sheet "BALANCE AGEE"?

Thanks

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bkourouma

ASKER

Thanks, I tried that but i was doing oSheet = oBook.Worksheets(0) for the first sheet. lol