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.


    your code goes between these tags


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


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

  • Thanks Roy but I am afraid I cannot "argue the toss" with the client. A lot of the people who will read it are older and very much set in their ways.

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

    Public Sub Write_To_File(sText As String, Optional sStyle As String = "No Spacing")
        oDoc.Paragraphs.Last.Range.InsertAfter sText
        oDoc.Paragraphs.Last.Range.Style = sStyle
        oDoc.Paragraphs.Last.Range.InsertAfter Chr(10)
    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.

Participate now!

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