| Subject | Re: Calculated field and Query |
| From | Dirk <test@test.com> |
| Date | Wed, 12 Dec 2018 19:53:03 +0100 |
| Newsgroups | dbase.getting-started |
Hello, Mervyn and tim The way tim proposed; because the feilds get a fixed place (example : oEXCcel.range("d11").select() oExcel.ActiveCell.FormulaR1C1 = "km" the loop only gives teh last values i suppose of the range if you oly take the first row : its works about Mervyn the works i only haveto get the info in colomms its belong, its a matter to put time on this question thanks for the other ways " difficult way is also a way" Dirk Op 11/12/2018 om 7:53 schreef Mervyn Bick: > 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. > | |