transferspreadsheet with formula

[George M]

I have Access exporting to an excel spreadsheet which is
then taken out in the field on a PDA. I need Access to
write a formula ("=D2-F2") in one column, 2 problems;
By the time its in the spreadsheet there is a single quote
in front of the = sign, so no calculation happens, AND
How can I increment the 2 to 3 to 4 etc in accordance with
the record count?
Much appreciated.

 

[Ken Snell]

One, you write a formula into an EXCEL cell by setting the .Formula property
of the cell, not the .Value. As in ACCESS VBA, if you don't specify, the
cell's default is .Value:
MyCell.Formula = "=D2-F2"

Two, you can use the .AbsolutePosition propertry of a DAO recordset for the
"record number" (minus 1, as this property is zero-based) -- that is, if I'm
understanding what you mean by "record count" in this context.

Alternatively, you might be able to use the EXCEL cell's Row property to do
the same thing, assuming that you're looping through the EXCEL cells' rows
in some way.