Passing a formula to Excel

[MikeC14081972]

I have a function that creates an Excel spreadsheet, from Access 2000, which is working fine. However I need to pass a formula to a specific cell.

I'm using the following code where balSrt is the first rown and balEnd is the last cell in the range.

.offset(irow,icol).formulaR1C1 = "=SUM(D" & balSrt & ":D" & balEnd & ")"

When this line is executed the cell shows the value #Name and the cell contains =SUM('D20':'D62')

My Question is how to remove the single quotes so Excel recognises the string as a formula.

Thanks

 

[SkipVought]

Hi,

You're not using R1C1 notation in your formula...

.offset(irow,icol).formula[s]R1C1[/s] = "=SUM(D" & balSrt & ":D" & balEnd & ")"

.offset(irow,icol).formula = "=SUM(D" & balSrt & ":D" & balEnd & ")"

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[MikeC14081972]

Many Thanks Skip.

That worked perfectly. I tried all sorts but not removing R1C1.

Thanks Again, what little hair I have left can stay that bit longer now.........lol