>How can I get a column total for columns 3 - 12 in a spreadsheet through automation. The number of rows is variable for each spreadsheet, however the columns are constant.
>
>I suppose I can calulate the totals and append a record to the table and then the totals will appear as the last entry when I add them to the worksheet. However I was looking for an automation type code.
There's a neat trick I learned from my last boss, but it isn't quite simple to explain. It needs several steps.
First, don't start with a blank sheet. Have a ready sheet with everything but the actual data already in it. Have a bunch of named ranges in it, that you can fill with
oXls.range("mynamedrange")=lcMyValue
That way you don't care about the coordinates of the cell where your value goes - Excel knows it by name.
The special range where you'll insert your rows should be two rows long and as wide as needed, depending on the number of columns you're inserting - and the second of those rows has minimal height. Before inserting your data, you need to insert reccount()-1 rows into that range (which I can't tell you how - that'd be proprietary code). The named range expands, and any formulae you had below it recalculate their ranges accordingly. So if you had a sum(f9:f10) there, and inserted nine rows, it becomes sum(f9:f19). Do _vfp.datatoclip(,,3), that will put your data on the clipboard, but you need to remove the first row, with field names, from it:
set memowidth to 2047
_cliptext=strtran(_cliptext, mline(_cliptext,1)+chr(13),"")
then paste this into this named range, using
oXls.range("mydetailsrange").pastespecial
And then you'll have what you need. It takes a bit more coding in advance, and you need to set your template sheet properly, but then you have an engine where you can just code to fill the sheets, not to design them in code.