How to Send Macro Commands to MS Excel Using DDEEXECUTE()

• Microsoft Visual FoxPro for Windows, version 3.0
• Microsoft FoxPro for Windows, versions 2.5 and 2.5a

SUMMARY

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.

MORE INFORMATION

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.

Sample Program

   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)

Notes

• To request something contained in the cell of a Microsoft Excel spreadsheet, a channel with a topic of that spreadsheet must be initiated.
• Make sure that the Microsoft Excel Ignore Remote Requests option is not in effect. To turn this option off, choose Workspace from the Options menu, and clear the Ignore Remote Requests check box.

REFERENCES

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