Excel: DDE Execute Fails with Some Macro Functions

Last reviewed: June 30, 1997
Article ID: Q105879

The information in this article applies to:

  • Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a

SUMMARY

Any command sent to Microsoft Excel over a dynamic data exchange (DDE) channel must be a command-equivalent macro function. "Command-equivalent" means that the function has an equivalent choice on one of the Microsoft Excel menus, such as choosing the Close command from the File menu or choosing the Delete command from the Edit menu. Command-equivalent functions are all designed to return a value of TRUE.

Commands such as GET.OBJECT() or DIRECTORY() are designed to return information other than TRUE. These commands are not accessible on the Microsoft Excel menus and therefore cannot be executed across a DDE channel. In addition, you cannot run a command in DDE if that command is added to the a menu with an add-in macro.

MORE INFORMATION

If another application must execute a function that has no command equivalent, the functions can be included in a Microsoft Excel macro that can then be run by the other application.

Microsoft provides macro examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This macro is provided 'as is' and Microsoft does not guarantee that the following code can be used in all situations. Microsoft does not support modifications of the code to suit customer requirements for a particular purpose.

For example, you could write the following macro in Microsoft Excel:

   A1:    =DIRECTORY()
   A2:    =CREATE.OBJECT(2,A1,0,0,E5)
   A3:    =RETURN()

Define this macro with the name "Testing" and save the macro sheet as TEST.XLM. (The first line of this macro returns the name of the current directory. The second line creates a rectangle that extends from cell A1 to E5.)

The DDE client can then execute the RUN() command to run the Testing macro before requesting the data from cells A1 and A2 of TEST.XLM. The syntax for this will vary with the application being used as the DDE client. As an example, here is the macro code to accomplish this from another instance of Microsoft Excel:

A1: chan=INITIATE("Excel","Test.xlm") A2: =EXECUTE(chan,"[run(""Test.xlm!testing"")]") A3: directory=REQUEST(chan,"R1C1") A4: ObjectID=REQUEST(chan,"R2C1") A5: =TERMINATE(chan) A6: =RETURN()

Explanation of Macro Code

A1: The INITIATE() command opens a channel from this instance of Microsoft Excel to the other.

A2: The EXECUTE() command runs the testing macro in the other instance.

A3: The first REQUEST() gets the information from cell A1 of the testing macro sheet, which will have the name of the current directory. The value is stored in the name "directory."

A4: The second request gets the object identifier returned by the CREATE.OBJECT() function and stores it in the variable named ObjectID.

A5: TERMINATE() closes the DDE channel.

NOTE: You must use R1C1 notation in any DDE conversation with Microsoft Excel. The double sets of quotation marks are necessary because they are part of the execute text string.

REFERENCES

"User’s Guide 2," version 4.0, page 303


Additional reference words: 3.00 4.00 4.00a
Keywords : IntpDde kbmacro kbprg kbprb
Version : 3.00 4.00 4.00a
Platform : WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 30, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.