Macros to Create DDE Connections Between WinWord and Excel

Last reviewed: July 30, 1997
Article ID: Q94713
The information in this article applies to:
  • Microsoft Word for Windows, versions 1.0, 1.1, 1.1a, 2.0, 2.0a, 2.0a-CD, 2.0b, and 2.0c
  • Microsoft Excel for Windows, versions 3.0 and 4.0
  • Microsoft Windows operating system versions 3.0 and 3.1

SUMMARY

This article contains four macros that use dynamic data exchange (DDE) to interact between Microsoft Word for Windows and Microsoft Excel.

Macro 1: Running Excel from Word for Windows Macro 2: Running Word for Windows from Excel Macro 3: Running an Excel Macro from Word for Windows Macro 4: Running a Word for Windows macro from Excel

Line explanations follow each macro.

Note: The line numbers at the beginning of each line of the macros are for reference only and are not part of the macro. Your macro will not run and error messages will occur if you retain the line numbers.

WARNING: ANY USE BY YOU OF THE CODE PROVIDED IN THIS ARTICLE IS AT YOUR OWN RISK. Microsoft provides this macro code "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

MORE INFORMATION

Macro 1: Running Excel from Word for Windows

This Word macro uses DDE to start Excel, insert data in an Excel spreadsheet, calculate the information, format the result, and insert it into a Word document.

  1. Sub MAIN 2. channel = DDEInitiate("excel", "sheet1") 3. DDEPoke channel, "R1C1", "12" 4. DDEPoke channel, "R2C1", "10" 5. DDEPoke channel, "R3C1", "=A1*A2" 6. DDEExecute channel, "[format.number(" + Chr$(34) +\ 7. "$#,##0.0" + Chr$(34) + ")]" 8. a$ = DDERequest$(channel, "R3C1") 9. DDETerminate channel
10. Insert a$ 11. End Sub

Line descriptions:

   2. From Word, open a DDE channel to Sheet1 in Excel.
3, 4. Insert values into spreadsheet cells.
   5. Insert function into spreadsheet. Excel calculates cell value.
   6. Format cell contents using the Format Number command in Excel.
   7. Assign the calculated and formatted value to a string variable
      in Word for Windows.
   8. Close the connection to Excel.
   9. Insert the string into the Word document.

Macro 2: Running Word for Windows from Excel

This sample Excel macro uses DDE to start Word for Windows, open and initiate a channel to the AUTOEXEC.BAT file, insert some text and the creation date, save the file as AUTOEXEC.BAK, and exit Word for Windows.

A1. =SEND.KEYS("~", TRUE) A2. =EXEC("c:\winword\winword.exe c:\autoexec.bat", 3) A3. =INITIATE("winword", "c:\autoexec.bat") A4. =EXECUTE(A3, "[insertbookmark .name=""test""]") A5. =EXECUTE(A3, "[insertfield .field = ""createdate \@ mm/dd/yy""]") A6. Backup created: A7. =POKE(A3,"test",A6) A8. =EXECUTE(A3, "[insertpara]") A9. =EXECUTE(A3, "[filesaveas .name=""c:\autoexec.bak""]") A10.=EXECUTE(A3, "[fileexit]") A11.=TERMINATE(A3) A12.=RETURN()

Line Descriptions:

 A1. Send the ENTER keystroke to the Convert File dialog box in Word.
 A2. Start and maximize Word and open the AUTOEXEC.BAT file.
 A3. Initiate a channel to the AUTOEXEC.BAT file.
 A4. Insert a bookmark in the document.
 A5. Insert the file creation date at current location.
 A6. Text string.
 A7. Insert a text string at the bookmarked location.
 A8. Insert a paragraph mark at the current insertion point location.
 A9. Save a copy of the file as "AUTOEXEC.BAK".
A10. Exit Word for Windows. A11. Terminate channel to Word for Windows.

Macro 3: Running an Excel Macro from Word for Windows

This Word macro creates a DDE link from Word for Windows (the client) to Excel (the server), runs an Excel macro called MACRO1.XLM and then terminates the connection. Before you run this Word macro, create the following two-line macro in cells A1 and A2 in Excel:

A1: Beep() A2: Return()

Save this macro as MACRO1.XLM. Excel must be open and MACRO1.XLM must be the active sheet when you run Macro 3 (if you want the macro to determine whether Excel is running, use the GetModuleHandle command from the Windows KERNEL library. For more information on GetModuleHandle, refer to the Microsoft Windows Software Development Kit (SDK) or page 108 of "Using WordBasic").

  1. Sub MAIN
2. channel = DDEInitiate("Excel", "c:\excel\macro1.xlm") 3. DDEExecute channel, "[run(" + Chr$(34) + "Macro1.xlm!R1C1" + \
   Chr$(34) + ")]"
4. DDETerminate channel 5. End Sub

Line Descriptions:

  1. Initiate a DDE channel with Excel.
3. Send the Excel Run command to Excel to execute the macro. 4. Terminate the channel to Excel.

Macro 4: Running a Word for Windows Macro from Excel

Before you run this Excel macro, create the following three-line global macro named "test" in Word for Windows:

Sub MAIN
MsgBox "DDE from Excel successful!"
End Sub

This Excel macro creates a DDE link from Excel (the client) to Word for Windows (the server), runs a Word for Windows Macro named "test", and then terminates the connection.

A1. =REGISTER("KERNEL","GetModuleHandle","IC") A2. =CALL(A1,"MSWORD") A3. =IF(A2 = 0) A4. =EXEC("winword.exe") A5. =END.IF() A6. =INITIATE("winword", "system") A7. =EXECUTE(A6,"[toolsmacro .name=""test"", .run]") A8. =TERMINATE(A6) A9. =RETURN()

Line descriptions:

A1-A2. Call the GetModuleHandle function to determine if Word for

       Windows is running.
A3-A5. If the call to GetModuleHandle returns a zero then start Word
       for Windows.
A6. Initiate a DDE channel with Word for Windows. A7. Run the "test" macro. A8. Terminate the DDE channel to Word for Windows.

Reference(s):

"Using WordBasic," by WexTech Systems and Microsoft, pages 102-115, 171-172


KBCategory: kbole kbmacro
KBSubcategory:
Additional query words: winword winword2
1.0 1.10 1.10a 2.0 2.0a 2.0a-CD 2.0b 2.0c
ole object linking dynamic data exchange embedding example
Version : 1.0 1.10 1.10a 2.0 2.0a 2.0a


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: July 30, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.