Excel: DDE Execute Fails with Some Macro Functions

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()

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