Quickest way to write to Word document from Excel VBA?

  • Hello. I am transforming Excel data into a Word file from the Excel file's VBA.


    I have included Word references and can produce the file but it runs very slowly! I wonder if anyone has tips on the fastest way to write to Word from Excel VBA?


    I have attached my file with the logic pared down to test and understand - the file and template Word doc need to be in the same folder.

    Speed Understand.xlsm Template.doc


    It writes the same data to the Word document 25 times in a loop to get an idea of the time taken. 25 writes takes about 10 seconds on my laptop so you can imagine when I try to run it on full reports it runs very slow!


    Any hints would be greatly appreciated. Thanks in advance.


    My pseudo code for the actual write to Word:

    • add the text at the current selection
    • select the text just added
    • set the style to that desired for the text
    • move the selection to the end of the document for next time
    • add a line break for formatting


    My code:



  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags

    Note: no apostrophe in the tags, just used for demonstration here.

    ['code]


    your code goes between these tags


    ['/code]


    Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.

  • Thanks Roy. Sorry I could not work out to format my code. Thanks for fixing it.


    I have a requirement to produce a Word file as it is more accessible for blind or partially sighted people who use a screen reader .


    Many thanks,

  • Hello. I believe the reason it was running so slow as I used Selection instead of a Word,Range. When I changed to this is was much quicker.


    So for reference I changed the slow subroutine above to:


    Code
    1. Public Sub Write_To_File(sText As String, Optional sStyle As String = "No Spacing")
    2. oDoc.Paragraphs.Last.Range.InsertAfter sText
    3. oDoc.Paragraphs.Last.Range.Style = sStyle
    4. oDoc.Paragraphs.Last.Range.InsertAfter Chr(10)
    5. End Sub

    After which it ran much quicker. Having a few issues now that the Style assignment sometimes is ignored but at least I have solved the speed issue!


    Thanks Roy for your time. Much appreciated.