How to Send Macro Commands to MS Excel Using DDEEXECUTE()ID: Q103087
|
The DDEEXECUTE() command cannot be used to directly send Microsoft
Excel macro commands to Microsoft Excel through dynamic data exchange
(DDE) if these macro commands are designed to return a value other
than TRUE.
See below for a sample program that illustrates how to send this type
of Microsoft Excel macro command from Microsoft FoxPro for Windows to
Microsoft Excel through DDE.
FoxPro includes several commands for sending DDE commands to other
applications that are written to be DDE aware. The DDEEXECUTE()
command is one of these commands. It can be used to send Microsoft
Excel macro commands to the Microsoft Excel spreadsheet program.
Many Microsoft Excel macro commands return the logical value TRUE if
they run successfully in a Microsoft Excel macro. Some Microsoft Excel
macro commands, including CREATE.OBJECT(), GET.OBJECT(), GET.NAME(),
GET.NOTE(), and most other GET.<command> macro commands, return
something other than TRUE. These type of Microsoft Excel macro
commands cannot be directly sent to Microsoft Excel through DDE from
FoxPro. These commands will not generate error messages; they will
just not work. To use these commands, you must run them in a Microsoft
Excel macro, which can be done from FoxPro through DDE. (See the
sample program below.)
The following sample program illustrates the problem of using these
commands through DDE. It also shows a way to execute these commands
successfully through DDE. This program returns the name of the chart
created with the CREATE.OBJECT() command in Microsoft Excel.
NOTE: This sample program illustrates many FoxPro DDE commands and
some Microsoft Excel macro commands. The use of these Microsoft Excel
commands is beyond the scope of Microsoft FoxPro Product Support. The
use of the FoxPro DDE commands is outside the scope of Microsoft Excel
Product Support.
CLEAR
RUN /N3 e:\xl4\excel.exe && The path should be modified
* to reflect the actual path to the Excel program.
=DDESETOPTION("timeout",10000) && Sets time-outs higher so
* Excel has time to load.
chan=DDEINITIATE('Excel','System')
=DDESETOPTION("timeout",2000) && Sets time-outs back down.
=DDEEXECUTE(chan,'[error(false)]') && Turns off error checking
* in Excel.
* These commands place some numbers in an Excel spreadsheet.
=DDEEXECUTE(chan,'[select("r1c1")]')
=DDEEXECUTE(chan,'[formula(10)]')
=DDEEXECUTE(chan,'[select("r[1]c")]')
=DDEEXECUTE(chan,'[formula(15)]')
=DDEEXECUTE(chan,'[select("r[1]c")]')
=DDEEXECUTE(chan,'[formula(20)]')
=DDEEXECUTE(chan,'[select("r[1]c")]')
=DDEEXECUTE(chan,'[formula(25)]')
=DDEEXECUTE(chan,'[select("r1c1:r4c1")]')
=DDEEXECUTE(chan,'[copy()]')
=DDEEXECUTE(chan,'[create.object(5,""r5c3"",0.75, ;
12,""r10c8"",47.25,11.25,1,true)]')
* The last command didn't work! It has to be run from
* an Excel macro that will be created and run next.
* Creating a macro in Excel!!
=DDEEXECUTE(chan,'[new(3)]')
=DDEEXECUTE(chan,'[select("r1c1")]')
=DDEEXECUTE(chan,'[formula("CreateChart")]')
=DDEEXECUTE(chan,'[select("r[1]c")]')
=DDEEXECUTE(chan,'[formula("=activate(""sheet1"")")]')
=DDEEXECUTE(chan,'[select("r[1]c")]')
=DDEEXECUTE(chan,'[formula("=copy()")]')
=DDEEXECUTE(chan,'[select("r[1]c")]')
=DDEEXECUTE(chan,'[formula("=create.object(5,""r5c3"",0.75, ;
12,""r10c8"",47.25,11.25,1,true)")]')
=DDEEXECUTE(chan,'[select("r[1]c")]')
=DDEEXECUTE(chan,'[formula("=return()")]')
* Go to the beginning of the macro and run it.
=DDEEXECUTE(chan,'[select("r1c1")]')
=DDEEXECUTE(chan,'[run()]')
* An invalid attempt to get the name of the embedded
* chart that was created with the Excel macro.
STORE "BLANK" TO zz && Initializing variable zz to "BLANK"
? zz && Display contents of variable zz
zz=DDEEXECUTE(chan,'[get.object(5)]')
? "zz is equal to"
? zz && The name of the object will not be
* in this variable.
* DDEEXECUTE() will not work with a GET.<command>.
* Selecting the macro sheet cell that contains the name
* of the chart that was created with the CREATE.OBJECT()
* Excel macro command.
=DDEEXECUTE(chan,'[activate("Macro1")]')
=DDEEXECUTE(chan,'[select("r4c1")]')
* Initiating another channel to Excel
=DDESETOPTION("timeout",10000)
channel=DDEINITIATE('Excel','Macro1')
=DDESETOPTION("timeout",2000)
* Here the name of the chart is copied and specially pasted
* into another cell in the macro sheet.
=DDEEXECUTE(channel,'[copy()]')
=DDEEXECUTE(channel,'[select("rc[1]")]')
=DDEEXECUTE(channel,'[paste.special(3,1,false,false)]')
* Here is where the name of the chart has been requested by
* FoxPro from the macro sheet cell that it was specially
* pasted into.
xx=DDEREQUEST(channel,"r4c2")
? "xx is equal to"
? xx && The name of the object will be in this variable.
=DDEEXECUTE(channel,'[cancel.copy()]')
* Terminating channels and quitting Excel
=DDETERMINATE(channel)
=DDEEXECUTE(chan,'[quit()]') && It may be desirable to
* remark this out to examine
* the worksheets in Excel more
* closely.
=DDETERMINATE(chan)
Microsoft Excel "User's Guide 2," version 4.0, Chapters 6 and 7
Microsoft Excel "Function Reference," version 4.0
Microsoft FoxPro for Windows "Language Reference," version 2.5, pages
L3-371 to L3-374, L3-363 to L3-367, and L3-382
Additional query words: VFoxWin FoxWin 2.50 2.5a
Keywords : kbcode FxinteropDde
Version : 2.50 2.50a
Platform : WINDOWS
Issue type : Last Reviewed: August 12, 1999