communicating with excel

[ramskkk]

I want to know basically
how to talk with excel thro' VB.net

[kamal patel]

you can communicate with excel through VB editor.

[Vijay.Tomar]

Connection to Excel using Jet Engine

To connect to Excel, one can use OleDb objects that will treat Excel as a database, and then the required information can be easily fetched by using SQL queries. The important steps that have to be considered while connecting to Excel are as follows:

* Connection String:

The connection string should be set to the OleDbConnection object. This is very critical as Jet Engine might not give a proper error message if the appropriate details are not given.

Syntax: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<Full Path of Excel File>; Extended Properties="Excel 8.0; HDR=No; IMEX=1".
* Definition of Extended Properties:
o Excel = <No>

One should specify the version of Excel Sheet here. For Excel 2000 and above, it is set it to Excel 8.0 and for all others, it is Excel 5.0.
o HDR= <Yes/No>

This property will be used to specify the definition of header for each column. If the value is ‘Yes’, the first row will be treated as heading. Otherwise, the heading will be generated by the system like F1, F2 and so on.
o IMEX= <0/1/2>

IMEX refers to IMport EXport mode. This can take three possible values.
+ IMEX=0 and IMEX=2 will result in ImportMixedTypes being ignored and the default value of ‘Majority Types’ is used. In this case, it will take the first 8 rows and then the data type for each column will be decided.
+ IMEX=1 is the only way to set the value of ImportMixedTypes as Text. Here, everything will be treated as text.

For more info regarding Extended Properties, check this out.
Loading of data in to Dataset

After successfully connecting to Excel using Jet Engine, it is easy to the load the data in to DataSet. One has to write a query similar to ANSI-92 with the only changes being that each Excel sheet will be treated as a table and the table name will be the sheet name with “$”. The range can also be specified after the “$” sign.
Collapse



Public Function ImportAttendence(ByVal PrmPathExcelFile As String, ByVal DataGrid1 As DataGrid)

Dim MyConnection As System.Data.OleDb.OleDbConnection

Try

''''''' Fetch Data from Excel

Dim DtSet As System.Data.DataSet

Dim MyCommand As System.Data.OleDb.OleDbDataAdapter



MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _

"data source='" & PrmPathExcelFile & " '; " & "Extended Properties=Excel 8.0;")

' Select the data from Sheet1 of the workbook.

MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]", MyConnection)



MyCommand.TableMappings.Add("Table", "Attendence")

DtSet = New System.Data.DataSet

MyCommand.Fill(DtSet)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

DataGrid1.DataSource = DtSet.Tables(0)

MyConnection.Close()

Catch ex As Exception

MyConnection.Close()

End Try

End Function


'**************
May be this code will help u.