Automating Word

Posted on by Dick Kusleika

JWalk comments: “…give me a daily dose of Excel. Preferably, something I don’t already know.” Of course, I don’t know anything that JWalk doesn’t already know, so I really had to wrack my brain.

To put selected Excel data into a Word document, you can automate Word and use Word’s bookmark feature. Bookmarks in Word are similar to named ranges in Excel. Before I present an example, I have to disclaim that I hate Word’s object model in no small part because I don’t know it very well. Therefore, the code dealing with Word’s OM may rightfully be harshly criticized.

First I create a Word document with some static text and a couple of place holders to mark where my Excel data will go. I bookmark the place holders (name and score, in this example) using Insert > Bookmark in Word.

WordAuto1

That done, I save the Word file to be opened later via Automation from Excel. Next I have some data in Excel that looks like this

WordAuto2

The purpose of my Excel macro will be to populate the sentence in the Word document with the name and score of the winner (assume it’s golf and the lowest score wins). The main code looks like this

Sub CreateWordDoc()

Dim wdApp As Object
Dim wdDoc As Object
Dim rCell As Range
Dim rRng As Range
Dim lScore As Long
Dim sName As String

Set rRng = Sheet1.Range("A2:A6")
lScore = 100 'set the max possible score

'loop through the range
For Each rCell In rRng.Cells
'if the score is less than previous
If rCell.Offset(0, 1).Value < lScore Then 'store the data as variables lScore = rCell.Offset(0, 1).Value sName = rCell.Value End If Next rCell 'open the word documents Set wdApp = CreateObject("Word.Application") Set wdDoc = wdApp.Documents.Open("C:AutoWord.doc") 'replace the bookmarks with the variables FillBookmark wdDoc, sName, "bmWinner" FillBookmark wdDoc, lScore, "bmScore", "0""" 'show the word document wdApp.Visible = True End Sub

This code calls a special function to replace the text inside of the bookmarks. When you replace the text inside bookmarks, the bookmarks go bye-bye. I have no idea why, they just do. So it's easier to write a little function to do the work and restore the bookmark when it's done.

Sub FillBookmark(ByRef wdDoc As Object, _
ByVal vValue As Variant, _
ByVal sBmName As String, _
Optional sFormat As String)

Dim wdRng As Object

'store the bookmarks range
Set wdRng = wdDoc.Bookmarks(sBmName).Range

'if the optional format wasn’t supplied
If Len(sFormat) = 0 Then
'replace the bookmark text
wdRng.Text = vValue
Else
'replace the bookmark text with formatted text
wdRng.Text = Format(vValue, sFormat)
End If

're-add the bookmark because the above destroyed it
wdRng.Bookmarks.Add sBmName, wdRng

End Sub

When the macro is run, you should get a Word document like this

WordAuto3

18 thoughts on “Automating Word”

  1. Hey Dick,

    How about avoiding those loops using MIN, INDEX and MATCH ?

    lScore = Application.Min(rRng.Offset(, 1))
    sName = Application.Index(rRng, Application.Match(lScore, rRng.Offset(, 1), 0))

  3. Juan: Definitely better (and probably faster). Sadly, I can never remember the syntax for Index so out of pure laziness, I didn’t use it.

  5. Hi Dick,

    I wanted to thank you for posting this example. It doesn’t take much imagination to see how this example could be altered to create all kinds of reports in Word that can bring in objects/data from any applications using the VBA object model. I work in manufacturing, and I could see how an outstanding monthly report with charts and tables could be generated quickly using a method like you showed above. What a time saver that would be.

    Thanks again,

    Vince

  6. “re-add the bookmark because the above destroyed it”

    From my notes:

    There are workarounds to the problem e.g. don’t change all the text but leave a trailing space, or cleverly use its start/end properties, or redefine the bookmark after it disappears. Or you could use a form field.

    Insert these using Word’s Forms toolbar. Double click it to show its properties. Give it a name in the ‘bookmark’ box e.g. RenewalDate. Give it a ‘label’ e.g. in the ‘Default text’ box – unfortunately, in wysiwyg mode you just see FORMTEXT but the label is seen in printpreview mode. Now assign the data to the formfield by executing:

    ActiveDocument.FormFields(“RenewalDate”).Result = “1 July 2002?

    It assigns the value and the formfield and the bookmark remains defined! You can reference the value elsewhere using the REF field i.e. { REF renewal_date } because the name of the formfield is also the name of the bookmark.

    Jamie.

    –

  7. The code seemed to run okay and changed the Word document. However, now I am getting a message that the document is read-only and do I want to create another copy.

    Is there a bug somewhere that the document is not closed properly. Now, I cannot delete or re-save over the Word document.

    Many thanks,

  8. Hello

    I tried to use this example, but the FillBookmark does not work.

    I get a error “Sub or function not defined”. Could you help me, what is wrong?

    Sub CreateWordDoc()

    Dim wdApp As Object
    Dim wdDoc As Object
    Dim rCell As Range
    Dim rRng As Range
    Dim lScore As Long
    Dim sName As String

    Set rRng = Sheet1.Range(“A2:A6?)
    lScore = 100 ‘set the max possible score

    ‘loop through the range
    For Each rCell In rRng.Cells
    ‘if the score is less than previous
    If rCell.Offset(0, 1).Value < lScore Then
    ‘store the data as variables
    lScore = rCell.Offset(0, 1).Value
    sName = rCell.Value
    End If
    Next rCell

    ‘open the word documents
    Set wdApp = CreateObject(“Word.Application”)
    Set wdDoc = wdApp.Documents.Open(“C:AutoWord.doc”)

    ‘replace the bookmarks with the variables
    FillBookmark wdDoc, sName, “bmWinner”
    FillBookmark wdDoc, lScore, “bmScore”, “0?

    ‘show the word document
    wdApp.Visible = True

    End Sub

    Br. Jan

  9. pwf: There is a sub and a function. The function got screwed up when I migrated servers, but it’s fixed now. Make sure you copy the function to your project and it should work.

  12. I know this is a few months later…
    In C# :

    object o1 = “Text1?;

    Word.Application wordApp = new Word.ApplicationClass();
    object fileName = @”C:yourfile.doc”;
    object missing = Type.Missing;

    Word.Document dCurrentDoc = wordApp.Documents.Open(ref fileName, ref missing,ref readOnly, ref missing,
    ref missing, ref missing, ref missing, ref missing,
    ref missing, ref missing, ref missing,
    ref isVisible);

    dCurrentDoc.Activate();

    string strg = wordApp.ActiveDocument.FormFields.Item(ref o1).Result;

    this will give you the contents of the FormField. Hint: make sure that the Document is Protected during addition of text to the FormFields otherwise the Formfield is deleted and only the text is left.

    Hope this helps someone…
    Mike

  13. I put the call to FillBookmark inside a For loop:

    FOR each bm in mainDoc.bookmarks
    …
    FillBookmark mainDoc, sName, “bmWinner”
    …
    next

    But, the bm never increments. What is going on here with the doc reference… How can I get the loop to work or why does it not go to the next bm?

    Ollie

  14. There are occasional instances in which For Each doesn’t work reliably in collections. I have read that “For Each co In ActiveSheet.ChartObjects” sometimes misses a chart, but I don’t recall ever seeing it, and I’ve done a few charts in my day. I do know I had problems with Word Bookmarks, and what I did was like this:

    Redim sBkmk(1 to .BookMarks.Count)
    For iBkmk = 1 to .BookMarks.Count
      sBkmk(iBkmk) = .BookMarks(iBkmk).Name
    Next

    and then I called each bookmark by name when processing it (and probably debug.printing its name to help track it).

  15. Love it. Over the years, I’ve come to beleive that if my boss(es) could think of it, it could be coded. Inserting data from an Excel document to a Word document via VBA was a challenage. I knew that the Word bookmarks were the key. Could not have done it without you. Thanks!!

  16. it works (well) with words, but i need do replace some graphics from excel to word… i marked the graphics in word with “bookmarks” but i don’t know the next step…

  17. Dear Dick Kusleika,

    Thank you so much for posting the above code. I would like to ask you kindly to help me on doing the opposite of the above code. I have bookmarks on word file(i.e. it is an assessment and it has different variable like: candidate name, position, score, etc.).

    Here i would to transfer those data to an excel sheet to keep them as record of all the candidates. Is there any VBA code will help to do so. I made an intensive search about that but unfortunately nothing found.

    Your support will be highly appreciated.
    Many thanks in advance for your help.

    Senan

  18. Jamie Collins:
    Super old post, I know. But your code to fill in the form fields worked like a charm. Exactly what I needed. Thanks much.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.