Solved

How to catch a VBA collection object into a Delphi array

Posted on 2004-03-27
8
372 Views
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
    Begin
      with item(i+1,emptyparam,emptyparam) DO
      begin
         ExcelNamesArray[I]    := name_;
         ExcelNamesArray_RC[I] := referstoR1C1;
         ExcelValuesArray[I]       := referstorange.value
      end;
    End;

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,

JGMS
0
Comment
Question by:JGMS
[X]
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
8 Comments
 
LVL 11

Expert Comment

by:shaneholmes
ID: 10694895

Listening.... <SMile>

Shane
0
 
LVL 11

Accepted Solution

by:
shaneholmes earned 250 total points
ID: 10694936
0
 
LVL 11

Expert Comment

by:shaneholmes
ID: 10694939
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:shaneholmes
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....


Shane
0
 

Author Comment

by:JGMS
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
0
 
LVL 11

Expert Comment

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

Shane
0
 

Author Comment

by:JGMS
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?

JGMS
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

733 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