Access to Exel creating variable formula

[MagicalDreams]

Hello I am sending data from access 2003 to Excel I then want to create a total sum for the data I have sent I can manage this if the data length is always the same length which it is not this worked when the data is the same

With gm_oExcelSheet.Rows(gCurrentExcelRow)
.Cells(, 7).value = "=SUM(R[-4]C:R[-1]C)"
End With

I then tried adding variables gStartRow as long and gEndRow as long. I have tried men variations on this but it is as if the variables do not work in the sum line

With gm_oExcelSheet.Rows(gCurrentExcelRow)
.Cells(, 7).ActiveCell.value = "=SUM(R[gStartRow]C:R[gEndRow]C)"
.Cells(, 7).value = "=SUM(R[gStartRow]C:R[gEndRow]C)"
End With

also tried it on its own gm_oExcel is my variable
Global gm_oExcel As Excel.Application

gm_oExcel.ActiveCell.FormulaR1C1 = "=SUM(R[gEndRow]C[-1]:R[gStartRow]C[-1])"
n
Any help would be great
John

[NeoPa]

Certainly .FormulaR1C1 is the correct property to set in these circumstances.
Another point to look out for is that the Format of these cells must NOT be Text (.NumberFormat = "@").
Last point I noticed, if gEndRow & gStartRow are variables in your Access VBA code then they should be coded something like :-

gm_oExcel.ActiveCell.FormulaR1C1 = "=SUM(R" & gEndRow & "C[-1]:R" & gStartRow & "C[-1])"

Hope this helps.

[MagicalDreams]

Certainly .FormulaR1C1 is the correct property to set in these circumstances.
Another point to look out for is that the Format of these cells must NOT be Text (.NumberFormat = "@").
Last point I noticed, if gEndRow & gStartRow are variables in your Access VBA code then they should be coded something like :-

gm_oExcel.ActiveCell.FormulaR1C1 = "=SUM(R" & gEndRow & "C[-1]:R" & gStartRow & "C[-1])"

Hope this helps.

Thank you for your help I realy apreciate it

John