[comp.windows.ms.programmer] Running Excel via a DDE link...

garrett@brahms.udel.edu (Joel Garrett) (04/04/91)

I would like to be able to activate a series of excel macros from within anothe
program via a DDE link.  An example of such a transaction would be like so:

* my program prepares some data (a nxm array)

* excel is started and loads a spreadsheet with macros in it, via a DDE request
  to excel, requesting data from a cell which contains a macro in it which will
  open a new worksheet to hold the incoming data and then returns the name of
  that sheet over the DDE link.

* my program sends the array of numbers to the new worksheet. (actually two
  steps, right? Send data, then execute a macro to do a 'paste link' into the
  empty worksheet, is this correct?)

* my program activates a macro which will perform a set of transformations or
  some other operation on the data.

Is this level of interaction possible without getting the user involved in
things?

						Thanks,
						Joel

						garrett@brahms.udel.edu

goodearl@world.std.com (Robert Goodearl) (04/06/91)

In article <20187@brahms.udel.edu> garrett@brahms.udel.edu(Joel Garrett)writes:
>I would like to be able to activate a series of excel macros from within
> another program via a DDE link.  An example of such a transaction would be 
> like so:
>
>* my program prepares some data (a nxm array)
>
>* excel is started and loads a spreadsheet with macros in it, via a 
>  DDE request
>  to excel, requesting data from a cell which contains a macro in it which
>  will
>  open a new worksheet to hold the incoming data and then returns the name of
>  that sheet over the DDE link.

This is all possible.  First off, you need to read the (admittedly poor)
documentation on DDE.  This is a message protocol with several types of
messages.

You can actually do the first step (start Excel with a spreadsheet spec.)
without DDE.  Simply make the appropriate API call to execute another
program.

Once Excel is running, you establish a conversation with the spreadsheet
via WM_DDE_INITIATE.  Then you can cause the execution of a macro within the
spreadsheet with WM_DDE_EXECUTE.  If this macro opens a new spreadsheet, it
would put the name of that spreadsheet in a cell as part of the macro.
Your program would then get the contents of that cell with a WM_DDE_REQUEST
message.

>
>* my program sends the array of numbers to the new worksheet. (actually two
>  steps, right? Send data, then execute a macro to do a 'paste link' into the
>  empty worksheet, is this correct?)

Not really.  (And it depends on what you want to do.)  The simpler method
involves using WM_DDE_POKE to update each spreadsheet cell with new data
from your program.  Then use WM_DDE_EXECUTE to initate the recalculation
and use WM_DDE_REQUEST to get the results.

Another method (with somewhat less message overhead on an ongoing basis)
would require two conversations.  One in which Excel is the Server and your
application is the Client and the other in which your application is the
Server and Excel is the client.  In this case, you establish hot links in
two directions.  An Excel macro would request updates of its cells whenever
your application changes the data for the cell (Using WM_DDE_ADVISE messages
from Excel to you.)  Your application would request updates from Excel when
the "result" data changed in the spreadsheet (using WM_DDE_ADVISE messages
from your app. to Excel.)

Then, as your app. generates new data, it sends a WM_DDE_DATA message to
Excel for each data change.  You can choose to have Excel recalculate for
each new data item, or do a manual recalculation initiated by a WM_DDE_EXECUTE
message.  Once Excel recalculates, any of the "result" cells that you have
an ADVISE state on will send WM_DDE_DATA messages back to your app.

>
>* my program activates a macro which will perform a set of transformations or
>  some other operation on the data.
>
>Is this level of interaction possible without getting the user involved in
>things?

Absolutely!  In fact, you can cause Excel to come up, minimize to an Icon
(I think), pass data back and forth, and shut down.  All without any user
interaction involved.

If you want to simplify your task of creating and manipulating the DDE
conversations, I suggest you check out the DDE support library provided by

    Horizon Technologies Inc.
         (517) 347-0800

I have not used the product, but the product description seems solid.
(I wrote the DDE support for EASEL/Win, Easel Corporation's GUI generation
language for Windows.)  And I found DDEWATCH, their DDE message monitor,
to be invaluable in debugging that DDE support and in debugging EASEL/Win
applications using DDE.
-- 
Bob Goodearl -- goodearl@world.std.com
Principal Software Engineer, Easel Corporation