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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[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
-------------------
"Claud Balls" <Li**@trainer.com> wrote in message
news:uj**************@TK2MSFTNGP09.phx.gbl...
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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Paul Clement]

On Wed, 23 Mar 2005 16:57:04 -0800, Claud Balls <Li**@trainer.com> wrote:

¤ 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)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Paul Clement]

On Thu, 24 Mar 2005 10:46:38 -0800, Claud Balls <Li**@trainer.com> wrote:

¤ 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)