Retrieving and updating cells in Excel with ODBC can be a fairly straightforward process. The advantages: Excel does not need to be running. (You don't even need it - although unpractical) The disadvantages: limited functionality. Keep in mind that there are limits. You cannot delete rows from Microsoft Excel worksheets or workbooks. You can clear data from individual cells in a worksheet, but you cannot modify or clear cells that contain formulas. You cannot create indexes on Microsoft Excel worksheets or workbooks. You cannot read encrypted data through the Microsoft Excel installable ISAM. The problem: "Microsoft Developer Support EMEA HOWTO: Use ADO and ADOX with Microsoft Excel Data from Visual Basic ID: Q257819 - Other ODBC Provider Connection Settings Column headings. By default it is assumed that the first row of your Excel data source contains columns headings, which can be used as field names. If this is not the case, you must turn this setting off, or your first row of data will "disappear" to be used as field names. This is done by adding the optional "FirstRowHasNames=" setting to the connection string. The default, which does not need to be specified, is "FirstRowHasNames=1", where 1 = True. If you do not have column headings, you need to specify "FirstRowHasNames=0", where 0 = False; the driver will name your fields F1, F2, etc. This option is not available in the DSN configuration dialog. However, due to a bug in the ODBC driver, specifying the FirstRowHasNames setting currently has no effect. In other words, the Excel ODBC driver (MDAC 2.1, 2.5) will always treat the first row in the specified data source as field names. Rows to Scan. Excel does not provide ADO with detailed schema information about the data it contains, as would a relational database. Therefore the driver must scan through at least a few rows of the existing data in order to make an educated guess at the datatype of each column. The default for "Rows to Scan" is 8 rows. You can specify an integer value from 1 to 16 rows, or you can specify 0 (zero) to scan all existing rows. This is done by adding the optional "MaxScanRows=" setting to the connection string, or by changing the "Rows to Scan" setting in the DSN configuration dialog." A solution: Create a System DSN based on the Excel Driver. Then edit the registry setting for FirstRowHasNames to 0. This can be done manually or programmatically. Assume a System DSN name oTest: HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.Ini\oTest\Engines\Excel\FirstRowHasNames=0 Now the first row will be data and field 'names' returned will be F1,F2,F3... Using Iczelion's ODBC example as a basis: connection string: strConnect db 'DSN=oTest;READONLY=False;DBQ=D:\WIN32ASM\ODBC\BOOK1.XLS;',0 Note: by default the spreadsheet is opened read-only. Note: when addressing an excel sheet, you must add a $ to the end of the sheet name. To get cell using SQLExecDirect: "select * from " (for cell D5.) "select * from " (for cells D5 thru D25.) i.e. SQLStatement db "select * from ",0 to put cell using SQLExecDirect: "update set = 'data'" i.e. SQLStatement db "update set = 'data'",0 Tested with odbcjt32 ver 4.00.4202.00 Of course if your data does have field(column) names the whole thing becomes even easier. And if your data has a unique key field, it's a cakewalk.
Posted on 2000-11-08 21:12:00 by wayne
Thanks wayne. That is exactly what I was looking for on Hiroshimators MB.
Posted on 2000-11-14 07:57:00 by me
Thanks, I put that to use today! I wouldn't even thought of that myself - thank you. bitRAKE
Posted on 2001-02-16 16:17:00 by bitRAKE