Subject Re: Calculated field and Query From Mervyn Bick <invalid@invalid.invald> Date Thu, 13 Dec 2018 11:39:38 +0200 Newsgroups dbase.getting-started On 2018-12-12 8:53 PM, Dirk wrote: > 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" Once you understand it, it's actually easier. :-) The trick is to get the fields in the rowset in the correct order.  As you have found, instead of using the * wild card you can specify the fields in whatever order you want.  You can place calculated fields anywhere in the field list.  If necessary a calculated field can be before or between the fields used in the calculation. If you have many fields with fairly long names you can save yourself a lot of work by using the SQL designer to set up the SELECT statement. You can select which fields you want and drag them up or down in the designer's output panel into the order you want.  Use the button with three dots to the left of each field to drag it up or down.  In the sourcecode editor add your calculated fields in the correct places.  In your query instead of typing out your SELECT statement use sql = "whatever.sql" You can, of course, simply place the values from the field in the spreadsheet and then make the spreadsheet calculate the values for the calculated columns.  This means skipping columns in the right places or writing code to insert columns.  Then you have to write code to place the correct formula for each calculated value.  It can be done but it will be a LOT more complicated than simply including the calculated values in the SQL SELECT statement in the first place. A cell in Excel can be specified as an absolute e.g oCell.range("d11") but it can also be specified by giving a row and column.  Instead of oExcel.range("d11").select()  you can use oExcel.range(11,4).select() The row and column can be held in variables e.g  nRow = 11 nCol = 4 (Column D) then oExcel.range(nRow,nCol).select() This second way of specifying cells makes it easy to loop through fields in a record and place the values in adjacent cells in the spreadsheet. qrf = q.rowset.fields // save some typing in the loop nRow = 11 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 //for each field in the rowset        oCell = oSheet.cells(nRow,nCol)        oCell.formula = qrf[n].value        nCol++ // move to next column for the next field      next      q.rowset.next() //Go to next record enddo Mervyn.