Using a excel range in an oledb select statement

Question

In VB.net, I'm reading one column of a larger excel spreadsheet into a datagrid using oledb. A normal select statement for reading an entire sheet would look something like this:

da2 = New OleDbDataAdapter("select * from [sheet3$]", cn)

For reading a single column from a larger sheet, this works:

da2 = New OleDbDataAdapter("select * from [sheet3$A:A" & lastrow & "]", cn) 'this reads the entire column A down to the integer value in the variable 'lastrow'

My goal, now, is to read the entire sheet with a loop, column by column, by turning the sheet reference into a string OR get vb.net to concatenate/cast a string and range (which it won't). I've tried these options, so far without success:

For colindex = 2 To lastcol3
colrange = xlWsheet3.Range(xlWsheet3.Cells(toprow, colindex), xlWsheet3.Cells(lastrow3.colindex)
addy = colrange.Address
da2 = New OleDbDataAdapter("select * from [sheet3$" & addy & "]", cn)

No matter what I try, it always comes down to the fact that the oledb adapter just won't reconcile a string with an excel.range. Is there an easier way to read a single column from excel by looping? I'm new to programming, so any advice would be helpful.

BG- BG- · Accepted Answer · 2014-11-12T00:24:10

I've ditched the oledb column-by-column loop, and instead have just gone with a straight excel.interop loop, via this post:

Filling DataGrid Columns w/ Excel Data in VB

I don't think oledb was meant for very detailed/complicated reading...

Using a excel range in an oledb select statement

1 Answers