Excel to Word very slow using DDE

  • 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:

    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.

  • Re: Excel to Word very slow using DDE


    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.

  • Re: Excel to Word very slow using DDE

    Well, I installed Word 2003 and Excel 2003 and it still runs slowly. Not as slowly but still unacceptable. One difference between your example is that mine uses 22 VLOOKUP's to populate itself. Also, I hide all rows where the VLOOKUP returns "" so the export process needs to handle this as well.

    I suppose I could copy the calculated range to another range as values and then copy the values to Word. Another experiment would be to delete the hidden rows and try it again; or just unhide all rows and give it a go. We'll see.

    The good news is that I now have the latest versions on my computer.

  • Re: Excel to Word very slow using DDE


    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.


  • Re: Excel to Word very slow using DDE

    Thank you very much. I tested your file and it works very quickly as advertised. Then I imported my data table into the workbook...40 mb's...and the program quickly bogged down as before. I am still using your test file; I just turned a 55kb workbook into a 40mb pig (or should I say hog?)

    The data worksheet is 40mb: 26 columns x 365 rows and full of =index and =Vlookup formulas. 7000 cells and each with =IF's so that the cell contains "" if the looked up value is null. One column links to a worksheet where each entry is well over 255 characters. Each of the 26 columns is linked to a separate workbook and some are very large.

    I thought that the problem might be related to automatic calculation so I added code to change to manual calculation prior to the exportation to Word. This did not make any difference.

    I then deleted 13 of the 26 columns which cut the wait time down to a minute. Then I cut it down to 6 columns and it took 9 seconds. Obviously, I need the data so do I have any alternatives???

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!