Using ADO.Net to query an Excel spreadsheet

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.

        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)