I'm trying to import an Excel file into a DataSet in VB. My code that I'm using for this looks like this:
Dim StrSql
Dim ExcelCon As New OleDbConnection
Dim ExcelAdp As OleDbDataAdapter
Dim ExcelComm As OleDbCommand
Dim Col1 As DataColumn
Try
ExcelCon.ConnectionString = "Provider=SQLNCLI11;" & _
"Data Source = " & strpath & _
";Extended Properties=""Excel 8.0;"""
ExcelCon.Open()
StrSql = "SELECT * From [$Sheet1$]"
ExcelComm = New OleDbCommand(StrSql, ExcelCon)
ExcelAdp = New OleDbDataAdapter(ExcelComm)
objDT = New DataTable()
ExcelAdp.Fill(objDT)
Col1 = New DataColumn
Col1.DefaultValue = 0
Col1.DataType = System.Type.GetType("System.Decimal")
Col1.Caption = "Sr No."
Col1.ColumnName = "SrNo"
objDT.Columns.Add(Col1)
Col1.SetOrdinal(1)
ExcelCon.Close()
Catch ex As Exception
Finally
ExcelCon = Nothing
ExcelAdp = Nothing
ExcelComm = Nothing
End Try`
The error I am receiving says "Invalid connection string attribute". Does anyone know how to fix this error? I am using Excel 2010, if that helps. Thanks in advance.