Excel Question

[SIMIN]

Hello all,
I just found a great sample code in MSDN which allows me to read data from Excel files.
But however, this is the 1st time I am going to do this, and cannot customize it exactly.
I want to open an Excel file, loop through all sheets and read all cells, rows and columns of each sheet one by one.

I have 2 samples here, taken from MSDN, but I cannot customize them to go through and show all cells/rows/columns of all sheets!
Please help me if you can Thanks.

Dim conn1 As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\ExcelData1.xls;Extended Properties=""Excel 8.0;HDR=YES""")
conn1.Open()
Dim cmd1 As New System.Data.OleDb.OleDbCommand("Select * From [EmployeeData$]", conn1)
Dim rdr As OleDbDataReader = cmd1.ExecuteReader
Do While rdr.Read()
    Debug.WriteLine(System.String.Format("{0,-10}{1, -15}{2}", _
       rdr.GetString(0), rdr.GetString(1), _
       rdr.GetDateTime(2).ToString("d")))
Loop
rdr.Close()
conn1.Close()

Dim conn2 As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\ExcelData2.xls;Extended Properties=""Excel 8.0;HDR=YES""")
Dim da As New OleDbDataAdapter("Select * From [InventoryData$]", conn2)
Dim ds As DataSet = New DataSet()
da.Fill(ds)
Dim dr As DataRow
For Each dr In ds.Tables(0).Rows Show results in output window
    Debug.WriteLine(System.String.Format("{0,-15}{1, -6}{2}", _
       dr("Product"), dr("Qty"), dr("Price")))
Next
conn2.Close()

 

[mandelbrot]

Hi Simin,


I think this is what youre looking for.

Oh, and while I think on, by changing what would be the table name in the SQL string, youre actually changing your worksheet in excel:

SELECT * FROM [myExcelWorksheetName]

Then its simply a case of moving through each of the rows (see the link that Ive put in above).


Paul.

 

[SIMIN]

Hi,
The problem is that I dont know what are the name of sheets/worksheets!

 

[mandelbrot]

Maybe this will help you - its not entirely related to what youre doing, but it may point you in an alternate direction...
Excel COM object

Kind regards,
Paul.