Excel reading in Delphi 5 with automation and excel 2007 running

3
votes

there was this piece of code in a delphi 5 program that i was debugging:

s:string
vararray:variant;

vararray := VarArrayOf(['']);
vararray := ExcelWorkSheet1.UsedRange[0].Value;
s := VarToStr(vararray);

This is used to perfectly work when Excel XP and 2003 were running. But ever since I changed to Excel 2007 (also windows7), delphi gives a variant type conversion error on the last line ('Invalid variant type conversion').

Can someone please help me out on this? This code has not been touched for the last 5 years and I am not able to find a good source that documents these components

thanks!

2
delphiexcelautomation
What does this cell contain? My bet is that it is a date/time held as a string. – David Heffernan
I ask the question because without knowing what this cell contains, it's very hard to answer – David Heffernan
ISTM that on the last-but-one line, vararray is being filled with the values in a range of cells and that there is a difference in how Excel 2003 and 2007 pass an array of values... Go at it with the debugger, check what is in vararray before the call to VarTOStr using both Excel 2003 and 2007. That should give a clue as to why to conversion error is occuring. Could also be related to regional settings wrt to argument separators and a change in behaviour between 2003/2007 in what they use (from where they pick up the separator to use). – Marjan Venema
documentation for Office COM servers resided within Office directory tree – Free Consulting
Can you reproduce this with a tiny sample Excel sheet and a tiny Delphi program? Then dump the content of the vararray variable. – Jeroen Wiert Pluimers

2 Answers

0
votes

I managed to get the following to work:

Sheet := Workbook.Sheets.Item[1] as ExcelWorksheet;
value := Sheet.Range['A1','B1'].Value;
Edit1.Text := value[1,1];
Edit2.Text := value[1,2];

Note that I am using early binding through the Excel2000 import unit. I think you are perhaps using late binding with CreateComObject('Excel.Application').

I don't understand your Range[1,2] syntax – that doesn't seem to meet the requirements of the Excel object model.

I hope this helps.

0
votes

UsedRange need not be active. If it isn't the .value will be empty and cannot be converted into a string.

Do extra checking, like so:

s:string
vararray:variant;

vararray := VarArrayOf(['']);
vararray := ExcelWorkSheet1.UsedRange[0].Value;
if vararray <> nul then s := VarToStr(vararray);
else s := '';