Excel: Communication Between Excel and Access Using DDE

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.

MORE INFORMATION

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

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 (you will need to alter the macro to specify the appropriate locations 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, choose Run from the Macro menu and choose OK.

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

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

1. 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("c:\excel\excel.exe c:\excel\macro1.xlm", 1)
             Chan = DDEInitiate("Excel", "System")
             DDEExecute Chan, "[Run(""macro1.xlm!Message"")]"
             DDETerminate Chan
         End Function
   
   

2. Run the macro. For more information on how to run the macro, see the "Running" topic under "Macros" in Microsoft Access Help.

The EXECUTE() function runs a macro named Message.

      Name Summary
      ------------

      Application Name: "Excel"
      Window Title: "Microsoft Excel"
      Topic: "System"
      Item: "[Run(""macro1.xlm!Message"")]"

REFERENCES

Microsoft Access README.TXT Microsoft Excel "Function Reference"