I have a spreadsheet which creates specific data for each day of the year one day at a time in a 2 column worksheet (by 10 to 15 rows which varies by day). I want to export January 1's data to a Word document, then generate January 2's data and export it to the same document, and so on for the entire year.
I use the following code to make it work, but it takes about 4 minutes per day which is unacceptible (I could do it manually far quicker). During this time Excel takes up 100% of the CPU. Here is the code:
Sub Copy2Word() ' ' Copy2Word Macro ' Macro recorded 11/7/2005 by Richard Macro to open Word ' ' Keyboard Shortcut: Ctrl+Shift+W channelNumber = Application.DDEInitiate( _ app:="WinWord", _ topic:="C:\Docs\John_Smith.DOC") Set rangeToPoke = Worksheets("DataInput").Range("Print_Area") Application.DDEPoke channelNumber, "\EndOfDoc", rangeToPoke 'The following code will be added which will add a blank line between each 'table in the Word document; currently each table is appended to the bottom 'of the previous day which causes some page break issues. Then save and 'close once all the days are completed. 'Selection.EndKey Unit:=wdStory 'Go to bottom of document ' Selection.TypeParagraph 'Insert paragraph 'Application.DDEExecute channelNumber, "[FILESAVE]" 'Application.DDEExecute channelNumber, "[FILECLOSE]" Application.DDETerminate channelNumber End Sub
I use Word/Excel XP BTW. Something is hogging the CPU here. I saw WillR's OLE posting elsewhere on this forum; since this DDE approach works I'd like to find a way to speed it up. If DDE is hopeless and OLE is the way to go then any help along this path would be appreciated. Thanks.