Can't set cell.formula in Excel XP but Excel 2000 is OK

  • Thread starter Thread starter Lars Gustavsson
  • Start date Start date
L

Lars Gustavsson

Hi!

I have created a VB 6.0 app that read/writes to Excel Templates via
OLE using late binding.

The main problem is that it seems impossible to write a formula to a
cell in sheet in Excel XP, but it works OK for one formula (or
reference) in Excel 2000.

The VB code looks like below,

'---
xlWB.Sheets("Report").Activate
xlWB.Sheets("Report").Range("B5").Select
xlWB.Sheets("Report").Range("B5").Formula = _
IIf(Left$(sFormula, 1) = "=", sFormula, "=" & sFormula)
'---

The formula in the example is like, sFormula = "='1'!B1"
and will only work in Excel 2000.

The formula in the example is like, sFormula =
"=Average('1'!B1;'2'!B1)"
it will won't work in either Excel versions.

How come? I would appreciate any comment on this.

TIA

Rgds

Lars, SWEDEN
 
the first formula should work in either version.

the second is using your regional list separator, so you need to use
FormulaLocal rather than Formula or you need to replace the semicolon with a
comma.
 
Back
Top