Hi,
I'm having a little trouble reading from a excel file into a datatable using the OledbConnection and OleDbDataAdpter. I get an error when calling the OleDbConnection.Open() method (""External table is not in the expected format") and the excel workbook I'm referencing isn't open. I dont get this error if the file is open. Am I doing something wrong or does the excel file need to be open for the connection to work Thanks in advance!
Code:
Private Function getSpreadSheetData(ByVal xlFileName As String) As DataTable Dim xlConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & xlFileName & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;""" Dim xlconn As New OleDbConnection(xlConnString) Dim xlda As New OleDbDataAdapter("select distinct G, J, M, N from [Arb Deals$] where E = 'USD'", xlconn) Dim xldt As New DataTable Tryxlconn.Open()
xlda.Fill(xldt)
Catch ex As ExceptionMsgBox(ex.Message)
Throw New Exception(ex.Message, ex.InnerException) Finallyxlconn.Close()
End Try Return xldt End Function
OleDbConnection to Excel
Andy Wilkinson
What happens if you remove the double-quotes from around the extended properties in the connection string I've got very similar code right now and it seems to be working fine.
TheViewMaster
Hi,
Could be a couple of things and it depends on your data.... Unless you have a heading row in row 1 that contains the field names then you don't need HDR=Yes in the connection string. It looks like you don't have this row as your referencing the columns using A, F, etc.. but I could be wrong.
let me know if that fixes things as there could be other problems. Also it would be good to post an example of your data as it could determine a few things with your connection strings and your select statement. It can be dummy data but seeing the structure of your data would be good.