Storing Cell values from Excel Sheet in an Array in VB.NET

[MKP]

When i am trying to store the values from excel sheets cells in an array (VB.NET) an error is shown.

Can you please comment such that the program runs successfully.

Thanx in Advance.

-------------------------------------------------------------------------

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim Cell1, Cell2, Distance As Double
Dim XC(), YC(), Dist() As Double
Dim Row As Integer

xlsheet = xlBook.ActiveSheet()

'For reading the cells (coordinates) from the excel sheet
'and calculating the distance ...

'This is without using Arrays and it is giving the
'Distance successfully...

Cell1 = xlsheet.Cells(StRow, 6).value
Cell2 = xlsheet.Cells(StRow, 7).value
distance = Math.Sqrt(Cell1 * Cell1 + Cell2 * Cell2)
Msgbox(Distance)

'Now i run a counter and read 10 cells from excel and storing
'in an array and then calculating distance and
'also storing that in an array... (It gives an error)

For row = 0 to 9

XC(Row) = xlsheet.Cells(Row, 6).value

'The error is: Exception from HRESULT: 0x800A03EC

YC(Row) = xlsheet.Cells(Row, 7).value

Dist(Row) = Math.Sqrt(XC(Row) * XC(Row) + YC(Row) * YC(Row))

MsgBox(Dist(Row))


Next

End Sub
-----------------------------------------------------------------------------

[Iouri]

You can svae excel into datatable and then get an array from there

'Establish a connection to the data source.
Dim sConnectionString As String
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\My Documents\Book2.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes"""
Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)
objConn.Open()

Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from [Sheet4$]", objConn)

Dim ds As New DataSet("ExcelDS")

da.Fill(ds, "ExcelSheet")

Dim dt As DataTable
dt = ds.Tables("ExcelSheet")

Dim drCurrent As DataRow
For Each drCurrent In dt.Rows
Console.WriteLine("{0} {1}", _
drCurrent("Col1").ToString, _
drCurrent("Col2").ToString)
Next

objConn.Close()

If the first row of the Excel Worksheet does not contain column names then remove the HDR=Yes
Extended Property from the connection string.