Subject Re: Calculated field and Query From Mervyn Bick <invalid@invalid.invald> Date Tue, 11 Dec 2018 08:53:34 +0200 Newsgroups dbase.getting-started On 2018-12-10 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.