[Automation] Why do my cells with formula appear as text?
 

We are currently automating Excel; creating a worksheet into which we are
pasting formula's. This generated sheet is opened by users (on a different
machine) which then causes the formula to be calculated.

This has been working fine up to now, until we changed the version of Excel
to 2002 (from 2000). All that happens now is that the formula appears as text
when the worksheet is opened.

Points to note:
1. The string holding the formula is added by the Cell.Formula property, e.g:
....
xlDataSheet.Cells(intRowNum, intCodeOfferCol).Formula = rsStocks!
txt_code_Offer
.....
2. The formula is "trimmed" so there are definitely no spurious characters.
3. If the Excel sheet is saved as a CSV file and opened it all works fine.
4. If the user presses <return the formula is recalculated!

An example of one of the formulas is:
=BLP|M!'MSDLEAFE INDEX,<SEDOL[PX_BID]'

Why do my cells with formula appear as text?
 

On Nov 15, 1:04 pm, Stuart wrote:
We are currently automating Excel; creating a worksheet into which we are
pasting formula's. This generated sheet is opened by users (on a different
machine) which then causes the formula to be calculated.

This has been working fine up to now, until we changed the version of Excel
to 2002 (from 2000). All that happens now is that the formula appears as text
when the worksheet is opened.

Points to note:
1. The string holding the formula is added by the Cell.Formula property, e.g:
....
xlDataSheet.Cells(intRowNum, intCodeOfferCol).Formula = rsStocks!
txt_code_Offer
.....
2. The formula is "trimmed" so there are definitely no spurious characters.
3. If the Excel sheet is saved as a CSV file and opened it all works fine.
4. If the user presses <return the formula is recalculated!

An example of one of the formulas is:
=BLP|M!'MSDLEAFE INDEX,<SEDOL[PX_BID]'

try this...

go to Tools - Options..
in "View" tab, uncheck the box against "Formulas"

HTH
Joe