Macros to Create DDE Connections Between WinWord and ExcelLast reviewed: July 30, 1997Article ID: Q94713 |
The information in this article applies to:
SUMMARYThis 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 WindowsThis 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.
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 ExcelThis 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 WindowsThis 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").
Chr$(34) + ")]"4. DDETerminate channel 5. End Sub Line Descriptions:
Macro 4: Running a Word for Windows Macro from ExcelBefore you run this Excel macro, create the following three-line global macro named "test" in Word for Windows:
Sub MAINMsgBox "DDE from Excel successful!" End SubThis 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
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |