VBA – Word – Update/Fill-in Document Form Fields

Have you ever needed to fill-in or update the form fields of a Word Document from say Access, Excel, … Below is a simple example of how you can do this. In it I demonstrate how to populate a textbox, as well as show you how you can check/uncheck a checkbox.

'---------------------------------------------------------------------------------------
' Procedure : UpdateDoc
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Update/Fill-in a Word document's form fields
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2010-Sep-17                 Initial Release
'---------------------------------------------------------------------------------------
Sub UpdateDoc()
'Requires a reference to the Word object library
Dim oApp        As Object 'Word.Application
Dim oDoc        As Object 'Word.Document
Dim sDocName    As String

On Error Resume Next
    Set oApp = GetObject(, "Word.Application") 'See if word is already running
    If Err.Number <> 0 Then     'Word isn't running so start it
        Set oApp = CreateObject("Word.Application")
    End If

On Error GoTo Error_Handler_Exit
    sDocName = "YourWordDocToOpenFullPathAndExtension"
    Set oDoc = oApp.Documents.Open(sDocName)
    oApp.Visible = True
    
    oDoc.FormFields("TextboxName").Result = "NewValue"      'Textbox
    oDoc.FormFields("CheckboxName").CheckBox.Value = True   'Checkbox

Error_Handler_Exit:
    On Error Resume Next
    oDoc.Close True
    oApp.Quit
    Set oDoc = Nothing
    Set oApp = Nothing
    Exit Sub

Error_Handler:
    If Err.Number = 5174 Then
        MsgBox "The specified file '" & sDocName & "' could not be found.", _
               vbCritical
    Else
        MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: UpdateDoc" & vbCrLf & _
               "Error Description: " & Err.Description, _
               vbCritical, "An Error has Occurred!"
    End If
    Resume Error_Handler_Exit
End Sub

7 responses on “VBA – Word – Update/Fill-in Document Form Fields”

  2. MikeC

    The oDoc.FormFields(“MyFieldName”) form field references do not appear to work in Word 2010. In fact, the below line of code returns a count of zero.

    Debug.Print ActiveDocument.FormFields.Count

    Is there a way to use VBA to update and manipulate form fields in Word 2010 or has Microsoft abandoned us?

    1. admin Post author

      My tests all worked fine with Access and Word 2010 so I suspect something else is going on.

      Also, to get the count your synthax should be: oDoc.FormFields.count

  3. Alberto

    Wonderful!
    Thank you!

    I’ve adjusted the code for my purpose, I have to retrieve the last row of an excel and fill the template word, then save with another name.
    I’ve figured that word file is opened every two times, not always
    first click –> open word
    then I close word
    click again on the macro button –> open word, open file, filling text, save file renamed, everything ok
    then I close everything
    next time the same…
    first click –> …

    this is my code, any tips & tricks??

    Sub UpdateDoc()
    ‘Requires a reference to the Word object library
    Dim oApp As Object ‘Word.Application
    Dim oDoc As Object ‘Word.Document
    Dim sDocName As String

    On Error Resume Next
    Set oApp = GetObject(, “Word.Application”) ‘See if word is already running
    If Err.Number 0 Then ‘Word isn’t running so start it
    Set oApp = CreateObject(“Word.Application”)
    End If

    On Error GoTo Error_Handler_Exit

    sDocName = (ThisWorkbook.Path & “/modello_offerta.dotx”)
    Set oDoc = oApp.Documents.Add(sDocName)
    oApp.Visible = True

    LastRow = ThisWorkbook.Sheets(“Offerte”).Cells.SpecialCells(xlCellTypeLastCell).Row

    oDoc.FormFields(“Numero_Offerta”).Result = ThisWorkbook.Sheets(“Offerte”).Range(“A” & LastRow).Value ‘Textbox
    oDoc.FormFields(“Name”).Result = ThisWorkbook.Sheets(“Offerte”).Range(“B” & LastRow).Value ‘Textbox
    ‘oDoc.FormFields(“CheckboxName”).CheckBox.Value = True ‘Checkbox

    With oDoc

    oDoc.SaveAs FileName:=(ThisWorkbook.Path & “/offerte/” & oDoc.FormFields(“Numero_Offerta”).Result & “_” & oDoc.FormFields(“Name”).Result & “.docx”)

    End With

    Error_Handler_Exit:
    On Error Resume Next
    ‘oDoc.Close True
    ‘oApp.Quit
    Set oDoc = Nothing
    Set oApp = Nothing
    Exit Sub

    Error_Handler:
    If Err.Number = 5174 Then
    MsgBox “The specified file ‘” & sDocName & “‘ could not be found.”, _
    vbCritical
    Else
    MsgBox “The following error has occured.” & vbCrLf & vbCrLf & _
    “Error Number: ” & Err.Number & vbCrLf & _
    “Error Source: UpdateDoc” & vbCrLf & _
    “Error Description: ” & Err.Description, _
    vbCritical, “An Error has Occured!”
    End If
    Resume Error_Handler_Exit
    End Sub