VBA automation code

VBA code is used to create and fill documents with data from the Access database using VBA automation. This code is generated by the document creation process wizard. This page explains code blocks commonly appearing in the generated code.

Create a document instance from the template

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

Transfer data from the source (form) into the document

Const cstrObjectName As String = "Invoice"
Dim frm As Form: Set frm = Forms(cstrObjectName)
With frm
    Dim wfr As New WordFiller: Set wfr.Document = doc
    wfr.FillElement Bookmark:="Address", value:=ControlValue(Control:=![Address])
    wfr.FillElement Bookmark:="AmountDue", value:=ControlValue(Control:=![Amount Due])
    wfr.FillElement Bookmark:="AmountMaterials", value:=ControlValue(Control:=![Amount Materials])
    wfr.FillElement Bookmark:="DueDate", value:=ControlValue(Control:=![Due Date], Format:="dd mmm yy")
    wfr.SetCheckbox Bookmark:="Extendedservice", value:=ControlValue(Control:=![Extended service])
    wfr.FillTableFromArray Bookmark:="LaborLinesubform", var2D:=arr2D

The WordFiller class handles the complexity of moving the data from the form into the marked positions in the document:

The ControlValue function

Public Function ControlValue(Control As Object, Optional UseDisplayValue As Boolean = True, 
                            Optional Format As String = "") As Variant

Get data from subforms to fill tables

Dim rs As Recordset
Dim intRow As Integer
Set rs = ![LaborLine subform].Form.RecordsetClone
ReDim arr2D(0 To rs.RecordCount - 1, 0 To 3)
intRow = 0
Do Until rs.EOF
    ![LaborLine subform].Form.Bookmark = rs.Bookmark
    arr2D(intRow, 0) = ControlValue(Control:=![LaborLine subform].Form![Labor])
    arr2D(intRow, 1) = ControlValue(Control:=![LaborLine subform].Form![Hours])
    arr2D(intRow, 2) = ControlValue(Control:=![LaborLine subform].Form![Rate Per Hour])
    arr2D(intRow, 3) = ControlValue(Control:=![LaborLine subform].Form![TotalLaborCosts])
    intRow = intRow + 1

Data to be transferred from a subform control source to a table in the document is first collected into an array. In the above case the 2nd dimension of the array is 0 to 3 because the wizard had been informed by the Template Designer that the number of columns to be filled is 4.

Save the new document

Dim strFullFileName As String
Const cstrExtension As String = ".docx"
Dim strFolder As String
Dim strFileName As String
strFileName = ReplaceIllegalCharacters(ControlValue(![CustomerID]), "_") & _
ReplaceIllegalCharacters(ControlValue(![Invoice Date], Format:="yymmdd"), "_")
strFolder = CodeProject.Path & "\Documents"
strFullFileName = strFolder & "\" & strFileName & cstrExtension
doc.SaveAs FileName:=strFullFileName, FileFormat:=wdFormatXMLDocument
Invoice_time_and_materials_Current = strFullFileName
RecordDocument strFullFileName, strTemplateFile, "Invoice_time_and_materials_Current"
doc.Saved = True

Code above shows the code produced based on choices made in the wizard in the demo concerning the folder and filename.

Save document as Pdf

In the wizard you can specify you want the document saved as type Pdf. This requires a different code for the saving process, the ExportAsFixedFormat method:


doc.ExportAsFixedFormat OutputFileName:=strFullFileName, & _ 
        ExportFormat:=wdExportFormatPDF, OpenAfterExport:=True

This involves an intermediate document in .docx format, which in most cases you will want to close and delete after the Pdf was created:

doc.Close False

Returns the full path of the saved document

Having the file full name returned is a key to integrating the document creation process into a larger process, for example having the document as an attachment for an email.

Invoice_time_and_materials_PDF_Current = strFullFileName

Recording the created document in an Access table

To have the document easily available in the Open Documents

Error handling

    Select Case Err.Number
    Case 5356 'Word cannot save this file because it is already open elsewhere
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation
        Resume Next
    Case 3021 'No current record
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation
        Resume HandleExit
    End Select
    ErrorHandle Err, "Invoice_time_and_materials_PDF_Current"
    Resume HandleExit
End Function

At the end of the document procedure error handling is inserted depending on the current applicable scenario as defined in the wizard.