Importing excel data w/ oledb

[Claud Balls]

I've been butting heads with this code, it always errors at
objAdapter1.Fill(objDataTable1)
Is oledb the best way to do this? How about ADO.NET?

Dim objDataTable1 As DataTable
Dim objConn As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& strFilePath & ";Extended Properties=Excel 8.0")
objConn.Open()
Dim strCmd As String = String.Format("SELECT * FROM [Freeze
Data$]")
Dim objCmdSelect As OleDbCommand = New OleDbCommand(strCmd,
objConn)
Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect

objAdapter1.Fill(objDataTable1)

objAdapter1.Dispose()
objCmdSelect.Dispose()
objConn.Close()
objConn.Dispose()

Return objDataTable1

 

[Ken Tucker [MVP]]

Hi,

Couple of things.

1) connection string. Should have hdr = yes; Use 8.0 for excel 97, 9.0 for
2000, 10.0 for 2002 (xp), 11.0 for 2003

OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& strFilePath & ";Extended Properties=Excel 8.0; HDR = YES;")

2) make sure you have the worksheet name correct

Ken
-------------------
I've been butting heads with this code, it always errors at
objAdapter1.Fill(objDataTable1)
Is oledb the best way to do this? How about ADO.NET?

Dim objDataTable1 As DataTable
Dim objConn As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& strFilePath & ";Extended Properties=Excel 8.0")
objConn.Open()
Dim strCmd As String = String.Format("SELECT * FROM [Freeze
Data$]")
Dim objCmdSelect As OleDbCommand = New OleDbCommand(strCmd,
objConn)
Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect

objAdapter1.Fill(objDataTable1)

objAdapter1.Dispose()
objCmdSelect.Dispose()
objConn.Close()
objConn.Dispose()

Return objDataTable1

 

[Paul Clement]

¤ I've been butting heads with this code, it always errors at
¤ objAdapter1.Fill(objDataTable1)
¤ Is oledb the best way to do this? How about ADO.NET?
¤
¤ Dim objDataTable1 As DataTable
¤ Dim objConn As OleDbConnection = New
¤ OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
¤ & strFilePath & ";Extended Properties=Excel 8.0")
¤ objConn.Open()
¤ Dim strCmd As String = String.Format("SELECT * FROM [Freeze
¤ Data$]")
¤ Dim objCmdSelect As OleDbCommand = New OleDbCommand(strCmd,
¤ objConn)
¤ Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
¤ objAdapter1.SelectCommand = objCmdSelect
¤
¤ objAdapter1.Fill(objDataTable1)
¤
¤ objAdapter1.Dispose()
¤ objCmdSelect.Dispose()
¤ objConn.Close()
¤ objConn.Dispose()
¤
¤ Return objDataTable1


What error do you get when you butt heads?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Claud Balls]

I've added HDR=YES, and I tried using Excel 9.0, since I have 2000, but
the open connection errors. The fill is still not working with a value
cannot be null message, which would lead me to believe my sql query
returned no results, which doesn't make sense, as it is pretty straight
forward:

Dim objCmdSelect As OleDbCommand = _
New OleDbCommand("SELECT * FROM [Freeze Data$]", objConn)

Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect
objAdapter1.Fill(objDataTable1)

 

[Paul Clement]

¤ I've added HDR=YES, and I tried using Excel 9.0, since I have 2000, but
¤ the open connection errors. The fill is still not working with a value
¤ cannot be null message, which would lead me to believe my sql query
¤ returned no results, which doesn't make sense, as it is pretty straight
¤ forward:
¤
¤ Dim objCmdSelect As OleDbCommand = _
¤ New OleDbCommand("SELECT * FROM [Freeze Data$]", objConn)
¤
¤ Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
¤ objAdapter1.SelectCommand = objCmdSelect
¤ objAdapter1.Fill(objDataTable1)

Actually HDR=Yes is not required unless the first row of the Worksheet contains column names.

I still don't have enough info with respect to your error message. Could you post the exact text?


Paul
~~~~
Microsoft MVP (Visual Basic)