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
Display More
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.