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]'

On Nov 15, 1:04 pm, Stuart <Stu...@discussions.microsoft.com> 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