ACC: Sample DDE Macro Communicates with Microsoft Excel 4.0

Article translations Article translations
Article ID: 97522 - View products that this article applies to.
This article was previously published under Q97522
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

Summary

Advanced: Requires expert coding, interoperability, and multiuser skills.

The text below demonstrates communication between two applications through dynamic data exchange (DDE). Specifically, one example shows a macro developed for Microsoft Excel version 4.0 for Windows that starts Microsoft Access, loads a database and runs a macro. A second example shows an Access Basic function that loads Microsoft Excel and runs a macro.

More information

Running a Microsoft Access Macro from Microsoft Excel

Perform the following three steps to demonstrate this behavior. These steps assume that a "MESSAGE" macro already exists in Microsoft Access.
  1. In Microsoft Excel, choose New from the File menu, select Macro Sheet and choose OK.
  2. Enter the following macro into the macro sheet. Substitute the appropriate location for files on your computer.
          =EXEC("c:\access\msaccess.exe c:\access\db4.mdb")
          Chan=INITIATE("MSACCESS","system")
          =APP.ACTIVATE("Microsoft Access")
          =EXECUTE(Chan,"MESSAGE")
          =TERMINATE(Chan)
          =RETURN()
    						
  3. To run the macro, select the first cell, select Run from the Macro menu and choose OK.
The EXEC function in the macro loads Microsoft Access minimized and loads the DB4.MDB database. The macro initiates a DDE channel and assigns it to the variable Chan. The APP.ACTIVATE switches to the Microsoft Access window to show the macro actions running.

The EXECUTE function runs a macro named "MESSAGE," as follows.
   Name Summary
   --------------------------------
   Application Name: "MSACCESS"
   Window Title: "Microsoft Access"
   Topic: "System"
   Item: "Message"
				

Running a Microsoft Excel Macro from Microsoft Access

Perform the following two steps to demonstrate this behavior. These steps assume that a macro named "Message" already exists in a Microsoft Excel macro sheet named "MACRO1.XLM."
  1. In Microsoft Access, create a new function that contains the following code. Substitute the appropriate location for files on your computer.
          Function CallExcel ()
             Dim Chan
             x = Shell("c:\excel\excel.exe c:\excel\macro1.xlm", 1)
             Chan = DDEInitiate("Excel", "System")
             DDEExecute Chan, "[Run(""macro1.xlm!Message"")]"
             DDETerminate Chan
          End Function
    						
  2. Create a new blank form and add a command button.
  3. Select the button.
  4. From the View menu, choose Properties.
  5. Set the button's OnPush property to the following:

    = CallExcel()

    Note that the OnPush property is named OnClick in Microsoft Access versions 2.0 and 7.0.
  6. View the form in Form view.
  7. Choose the command button to start the function.
The Shell function loads Microsoft Excel and the "MACRO1.XLM" macro sheet full screen and leaves the focus on Microsoft Excel. The macro initiates a DDE channel and assigns it to the variable Chan.

The EXECUTE function runs a macro named "Message."
   Name Summary
   -------------------------------------
   Application Name: "Excel"
   Window Title: "Microsoft Excel"
   Topic: "System"
   Item: "[Run(""macro1.xlm!Message"")]"
				

The syntax for these two macros demonstrates that the correct syntax for the "Item" depends on the target application.

References

"Microsoft Excel Function Reference", version 4.0, pages 14 - 15

Properties

Article ID: 97522 - Last Review: October 26, 2013 - Revision: 3.0
Applies to
  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Excel 4.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbinfo kbinterop KB97522

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com