ODBC and Excel

[Charles A. Lackman]

Hello,

I am using the following connection to Query and Excel Spreadsheet:

AConnectionString = "Driver={Microsoft Excel Driver
(*.xls)};DriverId=790;Dbq=" & ofdSelectFile.FileName & ";DefaultDir=c:\;"

ASourceConnection = New Odbc.OdbcConnection(AConnectionString)

Dim ADataAdapter as new odbc.odbcDataAdapter("SELECT * FROM $Sheet1",
ASourceConnection)

ADataAdapter.Fill(MyDataset)

This works Great, however, if a column in the Spreadsheet has a lot of blank
rows before pertinant data is available. The whole column is empty. i.e:

Column1 Column2
Column3
Row1 Tea
.59
Row2 Coffee
.97
.......
Row12 Milk 20081002
1.59

All the values work in Column1 and Column3, but Column2 is all blank. If I
put "0" in column2 - Rows 1 - 11 it works, however, I can't modify the
spreadsheet before the query...

This returns all blank entries also: "SELECT Column2 from $Sheet1"

Any Suggestions will be greatly appreciated,

Thanks

[Patrick Molloy]

can you make the datasource a named range. I use this with ADO and get good
results

Dim Conn As ADODB.Connection
Dim RST As ADODB.Recordset
Dim strConn As String
Dim SQL As String


sExcelSourceFile = "E:\Excel\Excel_database\Testdatabase.xls"

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel
8.0;"
strConn = strConn & "Data Source=" & sExcelSourceFile

Set Conn = New ADODB.Connection
Conn.Open strConn

Set RST = New ADODB.Recordset
SQL = "SELECT DISTINCT [PROD], [KEYV], [ID] FROM testdata"
...testdata is a range name on the sheet

you will need to set a reference to Microsoft Active Data Objects 2.6 Library

"Charles A. Lackman" wrote:

> Hello,
>
> I am using the following connection to Query and Excel Spreadsheet:
>
> AConnectionString = "Driver={Microsoft Excel Driver
> (*.xls)};DriverId=790;Dbq=" & ofdSelectFile.FileName & ";DefaultDir=c:\;"
>
> ASourceConnection = New Odbc.OdbcConnection(AConnectionString)
>
> Dim ADataAdapter as new odbc.odbcDataAdapter("SELECT * FROM $Sheet1",
> ASourceConnection)
>
> ADataAdapter.Fill(MyDataset)
>
> This works Great, however, if a column in the Spreadsheet has a lot of blank
> rows before pertinant data is available. The whole column is empty. i.e:
>
> Column1 Column2
> Column3
> Row1 Tea

> ..59
> Row2 Coffee
> ..97
> ........