Macros to Create DDE Connections Between WinWord and Excel

• 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

Line descriptions:

   2. From Word, open a DDE channel to Sheet1 in Excel.

   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".

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

   Chr$(34) + ")]"

Line Descriptions:

1. Initiate a DDE channel with 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

End Sub

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.

       for Windows.

Reference(s):

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