Subject 
Re: Calculated field and Query 
From 
Mervyn Bick <invalid@invalid.invald> 
Date 
Tue, 11 Dec 2018 08:53:34 +0200 
Newsgroups 
dbase.gettingstarted 
On 20181210 8:30 PM, Tim Ward wrote:
> On 10/12/2018 12:26, Dirk wrote:
>> so i have to find a way to loop the info
>
> Hi Dirk,
> If I wanted to mix table fields and calculated values into excel
> I'd use something like the loop below. Doing this way you don't need to
> have the table fields in the same order as the excel output and you can
> have calculated fields where you require them. Sorry if my Dutch is not
> good.
Twelve programmers, a dozen solutions. :)
I would prefer to make the calculations in the SQL statement with the
fields, including the calculated fields, in the required order. This
simplifies the code for placing the data in Excel.
Assuming we have a table test.dbf with the fields aantal and
eenheidsprijs and we also want to show the cost.
cWorkbook = 'd:\examples\test.xls' // As required
q = new query()
q.sql = 'select aantal,eenheidprijs,aantal*eenheidsprijs as kost from test'
q.active = true
qrf = q.rowset.fields //to save some typing later
oExcel = new oleAutoclient("Excel.Application")
oExcel.workbooks.add()
//oExcel.visible = true
oSheet = oExcel.ActiveSheet
// The following will place the fieldnames, including the calculated
// field's name, in the Excel worksheet as headings
//
// Alternatively replace with code to write headings to each column
nRow = 10
nCol = 8 //Column H
for n = 1 to qrf.size
oCell = oSheet.cells(nRow,nCol)
oCell.formula = qrf[n].fieldname
nCol++
next
// The following will loop through the rowset and place the data for
// each record on a new line in Excel
do while not q.rowset.endofset
nRow++ // Move down a row for each record
nCol = 8 // Start in column H for each record
for n = 1 to qrf.size
oCell = oSheet.cells(nRow,nCol)
oCell.formula = qrf[n].value
nCol++
next
q.rowset.next() //Go to next record
enddo
q.active = false
oExcel.activeWorkbook.saveAs(cWorkbook)
oExcel.activeWorkbook.close()
Mervyn.

