Manipulating Excel cells using Access code

[Sech]

Greetings all. I'm currently creating a database process that automatically updates various columns of an Excel spreadsheet using Access code and table data. However I have come across a couple of issues related to Excel formulas as follows -


1) How do I put a formula created in Access code into an Excel sheet cell?

I need to create the formula for each row in turn, so the current row is stored as a variable, as is the column letters as this will also change month to month due to the addition of extra hidden columns. I set the formula text to a string value and then attempt to copy this to the cell formula...

sFormula = "=SUM(H" & lRow & ":" & sDataColumn & lRow & ")"
oxlApp.Selection.FormulaR1C1 = sFormula

However this automatically adds in ' either side of the two cell descriptions, so instead of =SUM(H1:AD1) I get =SUM('H1':'AD1'). This errors and displays #NAME? in the cell rather than the formula result.


2) How do I set the result of an Excel formula to a variable rather than get the formula itself?

The column and row variables enable the relevant cell to be selected (this works fine). Then I set the cells formula to a string value.

oxlWorkSheet.Range(sDataColumn & lRow).Select
sDataValue = oxlApp.Selection.FormulaR1C1

However when I then display the string value it is "=SUM(H1:AD1)" not the result "22".


Can anyone help with either of these two problems?

 

[PHV]

Perhaps this ?
oxlApp.Selection.Formula[!]R1C1[/!] = sFormula

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886