| Roland Wingerter 2006-10-25, 7:34 am |
| Hi Eric,
Eric Logan wrote
>
> Incidentally, I recently found out how easy it is to use spreadsheet data
> as
> an ODBC data source. I tried several ways, and found that the following
> preparations of the XLS data make it convenient: a) Name each block of
> rows
> that you plan to use as a table. (Excel: insert name). Include the column
> names in each named block. Each named block will be seen by ODBC as a data
> table. The columns in each named block will automatically become the table
> columns. (You could have several tables in an XLS.))
------
Thanks for this information. FWIW, I've experimtented a bit and found out
how to name the blocks in the Excel file form dBASE via OLE.
See the sample code below.
Thanks again.
Roland
// Start Excel
oExcel = NEW OLEAUTOCLIENT("excel.application")
// oExcel.visible = true
oExcel.Workbooks.Open("c:\excel\fish.xls")
// Select last non-empty cell
oExcel.ActiveCell.SpecialCells(11).Select
// Save index of this cell to variable
cExObjTmp = oExcel.selection.address
// Insert name for this block
// Like Excel Menu Insert > Name > Define
oExcel.ActiveWorkbook.Names.Add("Table1",
oExcel.activesheet.range("A1:"+cExObjTmp))
//
oExcel.ActiveWorkbook.Save()
oExcel.ActiveWorkbook.close(0)
oExcel.quit()
|