How to catch a VBA collection object into a Delphi array
Posted on 2004-03-27
I want to improve the performance of a Delphi code that reads and writes from and to an Excel workbook.
In this workbook the cells of interest are given names, and these names thus form a collection object "Names" in VBA. Using VBA the names object can simply be read into a variant as an array by:
" Set MyArray = ActiveWorkbook.names ". However, in Delphi (D4,D7 and using ExcelXP) I was unsuccessful to the assignment done (MyArray defined as vaiant or OleVariant).
I have tried the statements : MyArray := Wrkbook.names as ExcelXp.names
or := Wrkbook.names as names
or := Wrkbook.names
or even := VarArrayof([Wrkbook.names]), all without success.
Instead I now use the following code to read in the
With WorkBk.Names DO
For I := 0 to N-1 DO
with item(i+1,emptyparam,emptyparam) DO
ExcelNamesArray[I] := name_;
ExcelNamesArray_RC[I] := referstoR1C1;
ExcelValuesArray[I] := referstorange.value
I'd rather read and write all as an array, but perhaps this is impossible. I don't know.
The cells in the workbook are non-adjacent (unfortunately) and what I want is to read their names, their value and their ReferstoR1C1 properties into fast Delphi arrays. After a Delphi treatment the data is to be written back into the named cells in Excel.
Can anybody put me on the right track to do this efficiently, to get a fast performance, since it concerns a large number of individual cells in Excel.
Many thanks already,