Microsoft KB Archive/99845

{| = Excel: Communication Between Excel and Access Using DDE =
 * width="100%"|

Last reviewed: September 12, 1996

Article ID: Q99845 The information in this article applies to:


 * Microsoft Excel for Windows, version 4.0
 * Microsoft Access version 1.0

SUMMARY
This article contains examples that demonstrate how Microsoft Excel and Microsoft Access can communicate through dynamic data exchange (DDE). The first example discusses a macro developed for Microsoft Excel version 4.0 for Windows that starts Microsoft Access, loads a database, and runs a macro. The second example discusses a Microsoft Access Basic function that loads Microsoft Excel and runs a macro.

Running a Microsoft Access Macro from Microsoft Excel
To run a Microsoft Access macro from Microsoft Excel, do the following (note that these steps assume that a macro called MESSAGE already exists in Microsoft Access):

 In Microsoft Excel, choose New from the File menu, select Macro Sheet and choose OK.  Enter the following macro into the macro sheet (you will need to alter the macro to specify the appropriate locations for files on your computer): =EXEC(&quot;c:\access\msaccess.exe c:\access\db4.mdb&quot;) Chan=INITIATE(&quot;MSACCESS&quot;,&quot;system&quot;) =APP.ACTIVATE(&quot;Microsoft Access&quot;) =EXECUTE(Chan,&quot;MESSAGE&quot;) =TERMINATE(Chan) =RETURN  To run the macro, select the first cell, choose 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 function 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: &quot;MSACCESS&quot; Window Title: &quot;Microsoft Access&quot; Topic: &quot;System&quot; Item: &quot;Message&quot;

Running a Microsoft Excel Macro from Microsoft Access
To run a Microsoft Excel macro from Microsoft Access, do the following (note that these steps assume that a macro named Message already exists in a Microsoft Excel macro sheet named MACRO1.XLM):

  In Microsoft Access, create a new function that contains the following text (you will need to alter the macro to specify the appropriate locations for files on your computer): Function CallExcel Dim Chan x = Shell(&quot;c:\excel\excel.exe c:\excel\macro1.xlm&quot;, 1) Chan = DDEInitiate(&quot;Excel&quot;, &quot;System&quot;) DDEExecute Chan, &quot;[Run(&quot;&quot;macro1.xlm!Message&quot;&quot;)]&quot; DDETerminate Chan End Function  Run the macro. For more information on how to run the macro, see the &quot;Running&quot; topic under &quot;Macros&quot; in Microsoft Access Help.

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: &quot;Excel&quot; Window Title: &quot;Microsoft Excel&quot; Topic: &quot;System&quot; Item: &quot;[Run(&quot;&quot;macro1.xlm!Message&quot;&quot;)]&quot; The syntax for these two macros demonstrates that the correct syntax for the &quot;Item&quot; depends on the target application.