Automated Word Template Creation With VBA

Question

I am creating a word document template and am at a bit of a crossroads. I would like to populate the document with figures created from MATLAB and Excel tables populated from MATLAB outputs. The figures are organized into folders and the Excel tables are organized in sheets in an Excel template as shown here:

I have asked several previous questions here with respect to automatically updating these tables and figures and now have the code for this:

Linked Table in MS Word

Linked Images and Tables in MS Word

The reports are long but vary in length. The reports document machine tests. Some clients test 1 machine, others test 5 machines. For 5 machines, the reports have 100 tables and 400 figures.

For example, the structure of the reports for 2 machines are as follows:

Text 1

Figure 1.1

Figure 1.2

Text 2

Table 1.1

Table 1.2

Figure 2.1

Figure 2.2

I would like to programatically create the report. The user would copy and paste the Word template, Excel template, and file structure into their working directory. There would be a worksheet in the Excel template with information about the test. i.e. number of machines to be tested. The template would be built for 1 machine.

The VBA would pull the number of machines to be tested from the Excel template. It would then index the figures and tables in the Word file, duplicate them for the specified number of machines in the correct location in the Word file, and link them to the correct source file locations. If an iteration of the test was ran, this is where I would use the code posted above to update the figures and tables.

What is the easiest way to set this up? What method will make generating and refreshing the table data the fastest? From the reading that I have done, it sounds like it might be faster to set up the tables to import as pictures rather than linking the data such as this application. I would like the code to be fast, foolproof, robust and not rely on any add ins such as this. It is possible I may need something like this but that seems a bit of overkill.

Any help would be much appreciated - I am trying to grasp the relationship between Word VBA, Field Codes, and Bookmarks and best use them to my advantage.

Answer 1

I would personally use Matlab code to create a LaTeX file that includes all filenames containing images and data.

During developpment, don't forget to regularly check that the produced latex is accepted by htlatex or oolatex.

Latex has a long learning curve, but if you have a month you will succeed.

Link about oolatex including filenames with images: https://groups.google.com/forum/#!topic/comp.text.tex/p--jBb7MIuQ

Answer 2

This problem is ideal for automation. To me it seems you should be able to have a basic template and fill in the information pretty much purely based on the Excel spreadsheet with the Machine information.

Bookmarks in Word are you friend here. You can use them to initially place your tables and figures and also to update them if new information becomes available (although this requires extra effort). I've done a fair bit of work importing data from Excel to Word as tables and would definitely recommend against importing tables as pictures. Your file size will balloon out really quickly and people that want to electronically extract data from the tables will want to stab you with rusty teaspoons.

From the information you've provided I'd probably begin your code in Excel with the Excel template as the active workbook. This is the way I'd set it up:

• Start with an add-in that displays a ribbon tab. This can be used for any 'machine' excel template.
• Use OLE automation to open Word and create a new document from the Word template (there is plenty of info on the net to do this)
• Read in the structure of the document from the Excel Template and set up the word document. Since you know the layout you can populate placeholders (bookmarks) for all of the figures and tables along with captions.
• Loop through all the tables and insert them first. The trick with inserting tablular data from Excel to Word is to insert the table as delimited text and then convert it to a table.
• Loop through and insert all the figures.
• If you encapsulate the figures with a bookmark and add a bookmark to the tables you can then update them individually as needed.

Note, that none of these options are that trivial by themselves. If you want to apply extra formatting like bold headings, merged cells or tables that split over pages then it is considerably more work.

You can use field codes to sequentially update table and figure numbers and again bookmarks to provide cross-references.

The question is quite broad to provide a lot of code, but the following example subs and functions should be enough to get you started. If you have further questions you should start a new question for them.

With an input of the Word Document (created from the Template and already populated with the bookmarks defining table locations) and the names of all the tables you have, the following functions will populate those tables into Word.

Sub PopulateTables(wdDoc As Word.Document, vTableArray As Variant)
    Dim ii As Integer, rInputData As Range

    'Loop through all the bookmarks
    For ii = LBound(vTableArray) To UBound(vTableArray)
        'Get the name of the current table from the list in the Excel template
        sTableName = vTableArray(ii)

        'Check if the bookmark exists in the document
        If wdDoc.Bookmarks.Exists("tblplc_" & sTableName) Then
            'Use the function to check if there is a table already at the bookmark
            Call CheckTableBookMark(wdDoc, "tblplc_" & sTableName)

            'Get the range of the information to be put into the table here.
            'THIS WILL BE YOUR OWN CUSTOM FUNCTION
            Set rInputData = GetMyInputData(sTableName)

            'Insert the data into Word
            Call CreateTableFromString(wdDoc.Bookmarks("tblplc_" & sTableName).Range, rInputData)
        End If
    Next ii
End Sub

This function will delete any existing table at a bookmark and ensure there is a fresh bookmark for new data:

Sub CheckTableBookMark(wdDoc As Word.Document, sTargetBM As String)
    'Function to delete any existing tables at a bookmark.

    With wdDoc
        .Activate
        .Bookmarks(sTargetBM).Select

        'If the bookmark has a table in it then we need to delete it
        If .Bookmarks(sTargetBM).Range.Tables.Count > 0 Then
            .Bookmarks(sTargetBM).Range.Tables(1).Delete

            'If the bookmark was 'inside' the table it may have been deleted.  Put it back in
            If Not .Bookmarks.Exists(sTargetBM) Then
                .Application.Selection.TypeParagraph
                .Application.Selection.MoveLeft Unit:=wdCharacter, Count:=1
                .Bookmarks.Add sTargetBM
            Else
                .Bookmarks(sTargetBM).Range.Select
                .Application.Selection.TypeParagraph
            End If

            'Do custom formatting here as required.
            .Bookmarks(sTargetBM).Range.Style = "Normal"
            .Bookmarks(sTargetBM).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
        End If
    End With
End Sub

The following two functions will Build a string containing the data and then convert it into a table for you:

Sub CreateTableFromString(ByRef rWordRange As Word.Range, rFromRange As Range)
    Dim tblWordTarget As Word.Table

    'Build the data from the Excel Spreadsheet and set it to the word range
    rWordRange.Text = BuildDataString(rFromRange)
    Set tblWordTarget = rWordRange.ConvertToTable(vbTab, AutoFitBehavior:=wdAutoFitFixed, DefaultTableBehavior:=wdWord8TableBehavior)

    'Do stuff with the table here (eg apply formatting etc)

    Set tblWordTarget = Nothing
End Sub

Function BuildDataString(rFromRange As Range) As String
    Dim sData As String, nrRow As Long, nrCol As Integer, iTotalColumns As Integer

    'Convert the input range to a variable and determine the number of columns
    vData = rFromRange.Value
    iTotalColumns = UBound(vData, 2)

    'Loop through all the elements in the array
    For nrRow = LBound(vData, 1) To UBound(vData, 1)
        For nrCol = 1 To iTotalColumns
            'Depending on what type of data is encountered either add it to the string or substitute something
            'You'll want to modify this as needed
            If IsError(vData(nrRow, nrCol)) Then
                sData = sData & "Error"
            ElseIf vData(nrRow, nrCol) = "" Or vData(nrRow, nrCol) = 0 Or vData(nrRow, nrCol) = "-" Then
                sData = sData & VBA.Chr$(150)
            Else
                sData = sData & vData(nrRow, nrCol - iIncrement)
            End If

            'Use tab delimiters for Word to know where the columns are
            If nrCol < iTotalColumns Then sData = sData & vbTab
        Next nrCol

        'Add a carriage return for each new line
        If nrRow < UBound(vData, 1) Then sData = sData & vbCr
    Next nrRow

    'Return the completed string
    BuildDataString = sData
End Function