After nearly pulling my hair out in frustration, I was finally able to get the Excel query working. The error I was getting: “Could not find installable ISAM” turned out to be my stupidity. The connection string needs to have the Extended Properties value quoted (notice double-quotes below:
' 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=" + Server.MapPath("./AMR_AppList.xls") + ";" + _
"Extended Properties=""Excel 8.0;"""
txtResult.Text = sConnectionString
' Create connection object by using the preceding connection string.
Dim objConn As New OleDbConnection(sConnectionString)
' Open connection with the database.
' 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.