WordFiller object – VBA Word document automation

Word document automation using VBA

VBA is the native programming language in Microsoft Office. As such, Word VBA is the common choice for automating Word document creation, filling a Word document with data from a data provider. In our case the data provider is Microsoft Access, either as the actual database or as intermediate to another data source.

The WordFiller object is used to fill or modify the content in bookmarked locations in a Word document. As you see on the left it provides an extensive set of high-level methods to implement all commonly used requirements. If you still find something missing, you can easily add it yourself or ask us to do it for you.

The WordFiller class module is added to your VBA project when you create an Word document automation process using Document Creation using Microsoft Access.

The first step in using WordFiller is to set the document it will be working on:

Dim doc As Word.Document
Set doc = appWord.Documents.Add(Template:=strTemplateFile, Visible:=True)
Dim wfr As New WordFiller: Set wfr.Document = doc

Bookmarks

With all below methods, the bookmark parameter accepts a string value identifying the Word Range to work on. Bookmarks normally correspond to the name of the field or bound control that provides the data. The BookmarkRange function provides a shortcut to the Range to operate on.

Methods

AddHyperlink

Adds a hyperlink on the bookmarked place

Public Sub AddHyperlink(Bookmark As String, Value As Variant, _
     Optional ParseValue As Boolean = False, _
     Optional TextToDisplay As String, _
     Optional SubAddress As String) 

• Value: Both text and hyperlink fields can be used as the source. In the former case the link address and the displayed text are equal, e.g. <a href="https://www.4tops.com"> https://www.4tops.com/</a>. With hyperlink fields the input has a syntax using # as separator: TextToDisplay#Address#SubAddress#ScreenTip where only the Address is mandatory. AddHyperlink is capable of translating this to a HTML Hyperlink <a> element.
• ParseValue: The # convention in hyperlink fields may also be used when supplying text values, e.g. That's us# https://www.4tops.com/ – for this you need in the email creation process VBA code specify ParseValue:=True as extra argument.
• TextToDisplay: Optionally set the display text for the hyperlink.
• SubAddress: In case you want to point to a bookmark on the page, e.g. https://www.4tops.com#mybookmark.

ApplyBoolean

Applies the first available behaviour default: range contains text …

Public Function ApplyBoolean(Bookmark As String, _
 Value As Boolean) As Word.Range

• Value: True or False

BookmarkRange

Returns the Word.Range object for a given bookmark. In addition to being the starting point for the methods implemented here, you can also use it as when writing your own document automation actions. If the bookmark supplied as argument in the function call does not exist an error is raised.

Function BookmarkRange(Bookmark As String) As Word.Range

Bookmark: string value indicating the Word bookmark.

FillElement

Inserts data at the bookmarked location.

Public Function FillElement(Bookmark As String, Value As String) As Word.Range

• Bookmark
• Value: the data to be inserted

FillListFromArray

Fills a list with the supplied items in Array1D. In addition to the usual presentation formats (numbered, bulleted) for a list this method can also be used to fill a text range: a, b and c. To be able to control the exact result of this text range fill the optional parameters have been supplied.

Public Sub FillListFromArray(Bookmark As String, Array1D As Variant, _
  Optional Separator As String = ", ", Optional Final As String = " and ")

• Bookmark
• Array1D: one dimensional array containing values that can be used as string.
• Separator: one or more characters used to separate the items in the list when fill a text range instead if a formatted list.
• Final: word or character used to separate the last item of the list from the previous.

FillOLEObject

Fills the bookmarked location with an OLEObject, for example a Chart in an Excel file – possibly produced in another document creation process.

Public Sub FillOLEObject(Bookmark As String, FileName As String, 
  Optional LinktoFile As Boolean = False, Optional DisplayAsIcon As Boolean = False)

• Bookmark
• FileName: The file from which the object is to be created. If this argument is omitted, the current folder is used.
• LinktoFile: True to link the OLE object to the file from which it was created. False to make the OLE object an independent copy of the file. 
• DisplayAsIcon: True to display the OLE object as an icon.

FillPicture

Fills the bookmark with a picture. The template Designer inserts a dummy image, which can be resized to indicate the intended size for the image to be inserted. By default, the image will be resized to have the same width as the dummy image in the template.

Other properties that are also copied from the dummy image are firstly its WrapFormat and also AlternativeText, Title, Flip, Hyperlink, PictureFormat and more.

The returned object is Shape or InlineShape.

Public Enum dcResizeMain
    NoResize = 0
    ResizeWidth = 1
    ResizeHeight = 2
End Enum
Public Function FillPicture(Bookmark As String, ImageFile As String, _
  Optional MainResize As dcResizeMain = ResizeWidth) As Object

• Bookmark
• ImageFile: The path and file name of the picture.
• MainResize:

FillPictureFromAttachment

Fill a picture location from a picture contained in an attachment control.

Public Function FillPictureFromAttachment(Bookmark As String, control As Attachment, _ 
       Optional MainResize As dcResizeMain = ResizeWidth) As Object

• Bookmark
• Control: the attachment control containing the pricture
• MainResize: specify how a picture for an attachment control is resized

FillTableFromArray

Fills a Word.Table from a 2-dimensional array, starting at the row which contains the bookmark. If the input array, here obtained from and Access subform, has a different order of columns from the table to fill, this can be specified using ColumnOrder which is automatically done by the Template Designer.

Public Function FillTableFromArray(Bookmark As String, Array2D As Variant, 
Optional ColumnOrder As String) As Word.table

• Bookmark
• Array2D: two dimensional array with values to put in the table.
• ColumnOrder: the order in which the columns are presented.

InsertFile

Inserts the content of a specified file on the bookmarked place. This allows you to maintain and use a collection of formatted text fragments from some folder on the network or the internet (OneDrive).

Public Sub InsertFile(Bookmark As String, FileName As String, _
  Optional Range As Variant, Optional ConfirmConversions = False, Optional Link = False)

• Bookmark
• FileName : The path and file name of the file to be inserted. If you don’t specify a path, Word assumes the file is in the current folder.
• Range: If the specified file is a Word document, this parameter refers to a bookmark. If the file is another type (for example, a Microsoft Excel worksheet), this parameter refers to a named range or a cell range (for example, R1C1:R3C4).
• ConfirmConversions: True to have Word prompt you to confirm conversion when inserting files in formats other than the Word Document format.
• Link: True to insert the file by using an INCLUDETEXT field.

SetCheckbox

Checks (True) or unchecks (False) the checkbox in the specified location. Note that each actual checkbox version is represented by two Symbols.

Returns a Word Range with a checkbox inside.

Public Function SetCheckbox(Bookmark As String, Value As Boolean, _
  Optional Extra As String) As Word.Range

• Bookmark
• Value: True or False
• Extra: text to add as label on the right side of the checkbox.

Strikethrough

In case the Value is False, strikes through the string value on the bookmark location. If True possible Strikethrough formatting is removed.

Public Function Strikethrough(Bookmark As String, Value As Boolean) As Word.Range

• Bookmark
• Value: True or False

UpdateAllFields

Updates any fields. This is particularly relevant because of the use of Fields in case a certain data item is applied more than once in the document. A bookmark name can only be used once in a document. If the same datum needs to be inserted in more locations, the Template Designer switches to using Fields with a reference to the bookmarked range. When a value is inserted using FillElement this does NOT automatically propagate to referencing fields. This is resolved by calling UpdateAllFields when the document filling is completed.

Public Sub UpdateAllFields()

UpdateAllTablesOfContents

Updates any TablesOfContents objects in the document. Usually at least the page numbers will need updating.

Public Sub UpdateAllTablesOfContents()

Properties

Document (Set/Get)

Set the Word Document on which the WordFiller methods will be applied. Makes the Word Document currently being filled available for additional actions.

Public Property Set Document(rData As Word.Document)
Public Property Get Document() As Word.Document