Excel to Word very slow using DDE

Re: Excel to Word very slow using DDE

Hi,

Using Excel 2003 and Word 2003 (with SP1) your macro seems to perform with no detectable delay for a 5 column 18 row print area with simple arithmetic formulas (having created a folder C:\Docs and a document John_Smith.doc etc)

If word is not open a request opens Word and includes the new data.

Dunno if this helps you, but there appears nothing greatly amiss with your idea.

Quote from bigblu89

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:

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.

Display More

Re: Excel to Word very slow using DDE

Hi,

Using the attached (the data isn't pretty, but it occupies space) takes about 10 seconds if Word is not open, and about a second if Word is open.

Of course this does not account for the time taken to proccess the input to column A if that is 'built-in' to your process, but the macro as displayed was used.

Cheers.