How to read Excel cell from VB.Net

Question

How can I read specific cell from Excel file using OLEDB Connection with VB.NET?

Can you show me sample code?

Answer 1

Try the following C# code:

Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection
MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=" & ExcelFilePath & "; " & _
"Extended Properties=Excel 8.0")

' Select the data from Sheet1 ([in-house$]) of the workbook.
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [in-house$]", MyConnection)

DS = New System.Data.DataSet
MyCommand.Fill(DS)
Dt = DS.Tables(0)
DataGrid1.DataSource = Dt

For particular cell try this (it will read cell D6). The point to note is that it is not using OLEDB connection rather it is directly accessing.

Namespace required using Microsoft.Office.Core;

Add it by adding reference from COM to Microsoft Office 12.0 Object Library

Dim oApp As New Excel.Application
Dim oWBa As Excel.Workbook = oApp.Workbooks.Open("c:\Test.XLS")
Dim oWS As Excel.Worksheet = DirectCast(oWBa.Worksheets(1),
Excel.Worksheet)
oApp.Visible = False

Dim oRng As Excel.Range
oRng = oWS.Range("D6")
MsgBox(oRng.Value)