Solved

How to catch a VBA collection object into a Delphi array

Posted on 2004-03-27
8
362 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
  • 5
  • 2
8 Comments
 
LVL 11

Expert Comment

by:shaneholmes
Comment Utility

Listening.... <SMile>

Shane
0
 
LVL 11

Accepted Solution

by:
shaneholmes earned 250 total points
Comment Utility
0
 
LVL 11

Expert Comment

by:shaneholmes
Comment Utility
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 11

Expert Comment

by:shaneholmes
Comment Utility
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
Comment Utility
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
Comment Utility
Did you try removing that from the uses clasue and compiling?

Shane
0
 

Author Comment

by:JGMS
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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 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…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now