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.
>