>I would like to enter a SUM formula in an Excel spreadsheet use automation. I will not know how many rows are to be summed so I want to enter the formula similar to Excel's AutoSum feature where it automatically finds the starting point of the column and fills in the formaula. Is there a way to do this?
>
>Thanks,
>Robert
Formulae should usually be entered with relative references. Also, it is simpler to enter them in the R1C1 syntax - this is what you get when you record an Excel macro, so you can (a) record a macro, (b) insert a sum, and (c) look at the results. Basically, you have to calculate how many rows you need, and replace the corresponding part of the formula with the number of rows. Thus, for adding the three cells above, Excel gives:
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
In Visual FoxPro, this becomes something like this, assuming you already have the variable to the current spreadsheet:
#define xlColumnE 5
oSheet.Cells(lnRow, xlColumnE).FormulaR1C1 = "=SUM(R[-" + trans(lnRowCount) + "]C:R[-1]C)"
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)
