Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.dotnet.framework.odbcnet,microsoft.public.excel.programming
|
|||
|
|||
ODBC and Excel
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 |
#2
Posted to microsoft.public.dotnet.framework.odbcnet,microsoft.public.excel.programming
|
|||
|
|||
ODBC and Excel
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 ........ 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export from Excel to DB2 via ODBC | Excel Discussion (Misc queries) | |||
Excel and ODBC | Excel Programming | |||
Q:Excel/ODBC/Login .... | Excel Programming | |||
Excel ODBC Query | Excel Programming | |||
using odbc in excel | Excel Programming |