How to catch a VBA collection object into a Delphi array

Posted on 2004-03-27
Medium Priority
Last Modified: 2010-04-05
Hi Experts,

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,

Question by:JGMS
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
LVL 11

Expert Comment

ID: 10694895

Listening.... <SMile>

LVL 11

Accepted Solution

shaneholmes earned 750 total points
ID: 10694936
LVL 11

Expert Comment

ID: 10694939

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 11

Expert Comment

ID: 10694955
Delphi creates a wrapper around the OCX, and because Names is an interface in delphi, you will need an interface construct for an enumeration object....


Author Comment

ID: 10696273
Thanks Shane,

I have looked at the thread an copied the sample code to get the VB collections in Delphi. It does not compile since "CollectLib_TLB" is missing.
Any suggestions how to proceed?

Regards, JGMS
LVL 11

Expert Comment

ID: 10696287
Did you try removing that from the uses clasue and compiling?


Author Comment

ID: 10697922
Yes I did.
When compiling it says:
[Error] UCollect.pas(57): Declaration of 'Next' differs from declaration in interface 'IEnumVariant'
The declaration function reads:
      function Next(celt: Longint; out elt; pceltFetched: PLongint): HResult; stdcall;
whereas down in the code the celt parameter was assigned of Integer type. However, making it LonInt does has no effect.
The same error remains.

I quess the type library is needed anyway, don't you?


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question