Accessing Excel Macros from VB6

[Rich]

I have been having speed problems with my VB6 application because it has to
read and write large amounts of data to two worksheets. It currently does
this accross processes.

I have decided to redesign the architecture of my app. I want to have an
object that will represent a spreadsheet (two tiered array). This will store
the data that is to be written to the spreadsheets when the data is being
processed. Once the data is ready to be written to the two spreadsheets
I want a macro within Excel to copy the two array objects to two worksheets,
hence there will be only one cross application call (the one to the macro).

I have four questions:

1) When I create an Excel object can I tell it to load a Macro?
(I know you can use addins that will load when Excel is started, but these
do not load when an Excel object is created from within VB6).

2) How do I call the macro in Excel from VB6 code?

3) Can a macro receive information as parameters? If they can't is there
another way of passing information to them?

4) Can the macro call procedures within my application? If yes, how?

If anyone can help answer any of these questions it would be greatly appreciated.

Regards
Rich

[Paul Clement]

On 19 Dec 2000 08:02:46 -0800, "Rich" <richard.bowen@power.alstom.com> wrote:

¤
¤ I have been having speed problems with my VB6 application because it has to
¤ read and write large amounts of data to two worksheets. It currently does
¤ this accross processes.
¤
¤ I have decided to redesign the architecture of my app. I want to have an
¤ object that will represent a spreadsheet (two tiered array). This will store
¤ the data that is to be written to the spreadsheets when the data is being
¤ processed. Once the data is ready to be written to the two spreadsheets
¤ I want a macro within Excel to copy the two array objects to two worksheets,
¤ hence there will be only one cross application call (the one to the macro).
¤
¤ I have four questions:
¤
¤ 1) When I create an Excel object can I tell it to load a Macro?
¤ (I know you can use addins that will load when Excel is started, but these
¤ do not load when an Excel object is created from within VB6).
¤
¤ 2) How do I call the macro in Excel from VB6 code?
¤
¤ 3) Can a macro receive information as parameters? If they can't is there
¤ another way of passing information to them?
¤
¤ 4) Can the macro call procedures within my application? If yes, how?
¤
¤ If anyone can help answer any of these questions it would be greatly appreciated.

See if the following helps:

http://support.microsoft.com/support.../Q153/3/07.ASP

The answer to question four might be a bit more complicated, but you should be able to use
WithEvents to do this. Here is an example using Microsoft Word.

http://support.microsoft.com/support.../Q172/0/55.asp


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)