Example to Read an Excel file using ADO.net using Excel file as Datasource using VB.net

******Example to Read an Excel file using ADO.net using Excel file as Datasource*******

' Create connection string variable. Modify the "Data Source" parameter as
' appropriate for your environment.
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=c:\ExcelData.xls" _
& ";" & "Extended Properties=Excel 8.0;"

' Create the connection object by using the preceding connection string.
Dim objConn As New OleDbConnection(sConnectionString)

' Open connection with the database.
objConn.Open()

' The code to follow uses a SQL SELECT command to display the data from the worksheet.
' Create new OleDbCommand to return data from worksheet.
Dim objCmdSelect As New OleDbCommand("SELECT * FROM [Sheet1$]", objConn)

' Create new OleDbDataAdapter that is used to build a DataSet
' based on the preceding SQL SELECT statement.
Dim objAdapter1 As New OleDbDataAdapter

' Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect

' Create new DataSet to hold information from the worksheet.
Dim objDataset1 As New DataSet

' Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "XLData")
objDataset1.WriteXml("C:\testxmlfile.xml", XmlWriteMode.WriteSchema)

' Build a table from the original data.
DataGrid1.DataSource = objDataset1.Tables(0).DefaultView
DataGrid1.DataBind()

' Clean up objects.
objConn.Close()