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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top