Read Excel File into ADO.Net Dataset without using Excel Automation

Need a quick way to suck an Excel file into an ADO.Net dataset without having to muck around with Excel Automation? Here's how.

' -- begin declare variables
Dim ds As DataSet
Dim dtTables As DataTable
Dim cn As System.Data.OleDb.OleDbConnection
Dim da As System.Data.OleDb.OleDbDataAdapter
Dim cmd As System.Data.OleDb.OleDbCommand
Dim strTable As String
Dim strXLConnString As String
' -- end declare variables

strXLConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=""YOUREXCELFILENAMEHERE"";Extended Properties=""Excel 8.0;IMEX=1;MaxScanRows=500;"""

' -- get tables (sheets) from our excel file
cn = New System.Data.OleDb.OleDbConnection(strXLConnString)
cn.Open()
dtTables = cn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, Nothing)

' -- now get the data: Rows(0) indicates first worksheet available.
strTable = dtTables.Rows(0)("TABLE_NAME").ToString
cmd = New System.Data.OleDb.OleDbCommand
cmd.Connection = cn
da = New System.Data.OleDb.OleDbDataAdapter(cmd)

' -- adjust your sql below if you know field (column) names
ds = New DataSet
cmd.CommandText = "SELECT * FROM [" & strTable & "]"
da.Fill(ds)

And that's it. The dataset ds now contains the data from the excel sheet.