>>>>>>Hi All,
>>>>>>
>>>>>>This is my 1st attempt at OLE Automation. I have a table from a x-tab query that I would like to transfer to an Excel worksheet for viewing, maybe even create a report with Text and company logo. I'm a little confused, do I use CREATEOBJECT('Excel.sheet') and export the data to the worksheet?? VFP help is a little vague in the matter.
>>>>>>
>>>>>>TIA
>>>>>
>>>>>select yourtable
>>>>>copy to c:\temp\test.xls type xls
>>>>>oExcel=createobject("Excel.Application")
>>>>>oExcel.Workbooks.open("c:\temp\test.xls")
>>>>>oExcel.visible=.t.
>>>>
>>>>Thanks very much,
>>>>
>>>>One question though. I'm trying to get the sum of each field to append as the last record in the table. Here's what i have:
>>>>
>>>>select xtab
>>>>go bottom
>>>>append blank in xtab nomenu
>>>>for lnCnt = 2 to fCount() && 2nd field is the 1st numeric field
>>>> lcFieldName = field(lnCnt)
>>>> replace &lcFieldname with sum(lcFieldName)
>>>>endfor
>>>>
>>>>should I add the table name lcFieldName = 'myTable.'+Field(lnCnt)??
>>>>
>>>>TIAA
>>>
>>>As far as I remember, vfpxtab.prg accepts some parameters to create total rows,columns. In regard to your code: I would collect all CALCULATE SUM() to array (inside the same loop) and then INSERT ... FROM ARRAY
>>
>>Thanks Ed,
>>
>>The array's a good idea, I have anywhere between 15 and 70 columns to total depending on the parameters of the SQL select.
>>
>>FYI, vfpxtab.prg will add a column for the row totals, but won;t add a row for column totals.
>
>John gave you right idea: SELECT SUM()... INTO ARRAY ... and then INSERT ... FROM ARRAY...


This is what I tried instead, not that I don't appreciate the advice, but the number of fields vary depending on the department using the software:

for lnCnt = 2 to fCount()
lcFieldName = 'xtab.'+field(lnCnt)
calculate sum(&lcFieldName) to lnSum
replace &lcFieldname with lnSum
endfor

When i run this lnSum changes along with the sum of each field, the replace won't replace the field with the variable value, except once when I tried it in the debugger, then it wouldn;t do it again.

A little side note. If I wanted to add some text into my Excel worksheet, how could I manage the rows? Text A in row 1, Text B in row 2 and my data to start at say row 6 for example.