Excel type library / Delphi 2009/ bad variable type

Question

We have just updated an application which uses the Excel type library to Delphi 2009 from Delphi 2006. What I am finding is that nearly nothing now works - nearly any call to Excel results in a message "Bad variable type.". For example we may do: Sheet.Cells.Item[Row, Column].Value := Value where Sheet is an ExcelWorksheet and Value is a Variant. The next thing we see in the stack is a call to _DispInvoke in Variants, and then on into ComObj. What am I doing wrong?

Answer 1

I can advise you to recreate/reimport the type library if either the Excel or the Delphi version has changed. Using a TLB from another versions often gives these problems.

Answer 2

It turned out that we had a generic procedure to set value of a cell of the form:

procedure SetValue(aSheet: ExcelWorksheet; aRow, aCol: Integer; aValue: Variant)
begin
  aSheet.Cells.Item[aRow, aCol].Value := aValue;
end;

this fails with the error I mentioned. If I change this to:

procedure SetValue(aSheet: ExcelWorksheet; aRow, aCol: Integer; aValue: Variant)
var
  sValue: WideString;
begin
  sValue:= aValue;
  aSheet.Cells.Item[aRow, aCol].Value := sValue;
end;

To me this suggests that Delphi 2009 is doing something different with variants (than D2006 was at least), something that COM doesn't like, unless someone has a better explanation? Anyway I'm happy to muddle on with this workaround for the time being.