Importing excel data w/ oledb

Claud Balls

unread,

Mar 23, 2005, 7:57:04 PM3/23/05

to

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]

unread,

Mar 23, 2005, 11:09:42 PM3/23/05

to

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:ujdBmxAM...@TK2MSFTNGP09.phx.gbl...

Paul Clement

unread,

Mar 24, 2005, 9:04:25 AM3/24/05

to

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

unread,

Mar 24, 2005, 1:46:38 PM3/24/05

to

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 ***

Paul Clement

unread,

Mar 24, 2005, 2:14:27 PM3/24/05

to

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?