This article was previously published under Q97522
This article has been archived. It is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.
The text below demonstrates communication between two applicationsthrough dynamic data exchange (DDE). Specifically, one example shows amacro developed for Microsoft Excel version 4.0 for Windows thatstarts Microsoft Access, loads a database and runs a macro. A secondexample shows an Access Basic function that loads Microsoft Excel andruns a macro.
Running a Microsoft Access Macro from Microsoft Excel
Perform the following three steps to demonstrate this behavior. Thesesteps assume that a "MESSAGE" macro already exists in MicrosoftAccess.
In Microsoft Excel, choose New from the File menu, select Macro Sheet and choose OK.
Enter the following macro into the macro sheet. Substitute the appropriate location for files on your computer.
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 andloads the DB4.MDB database. The macro initiates a DDE channel andassigns it to the variable Chan. The APP.ACTIVATE switches to theMicrosoft Access window to show the macro actions running.
The EXECUTE function runs a macro named "MESSAGE," as follows.
Running a Microsoft Excel Macro from Microsoft Access
Perform the following two steps to demonstrate this behavior. Thesesteps assume that a macro named "Message" already exists in aMicrosoft Excel macro sheet named "MACRO1.XLM."
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
Create a new blank form and add a command button.
Select the button.
From the View menu, choose Properties.
Set the button's OnPush property to the following:
Note that the OnPush property is named OnClick in Microsoft Access versions 2.0 and 7.0.
View the form in Form view.
Choose the command button to start the function.
The Shell function loads Microsoft Excel and the "MACRO1.XLM" macrosheet full screen and leaves the focus on Microsoft Excel. The macroinitiates a DDE channel and assigns it to the variable Chan.
The EXECUTE function runs a macro named "Message."