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