index > Visual Basic for Applications (VBA) > OleDbConnection to Excel

OleDbConnection to Excel

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

Try

xlconn.Open()

xlda.Fill(xldt)

Catch ex As Exception

MsgBox(ex.Message)

Throw New Exception(ex.Message, ex.InnerException)

Finally

xlconn.Close()

End Try

Return xldt

End Function

JeffJ

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.

www.dsmyth.net/blog

Derek Smyth

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.

a little trolley is a dangerous thing for example you could trip over it

duck thing

reply 3