Automatically create a Word template using VBA

Question

I am creating a document template with text and a little at the crossroads. I would like to fill out a document with numbers created from MATLAB and Excel tables populated from MATLAB outputs. The numbers are organized into folders, and Excel tables are organized as sheets in an Excel template, as shown below:

I asked a few previous questions regarding the automatic updating of these tables and numbers and now I have the code for this:

Linked table in MS Word

Related Images and Tables in MS Word

The reports are long, but vary in length. Testing machine test reports. Some customers test 1 car, others test 5 cars. For 5 machines, reports contain 100 tables and 400 digits.

For example, the report structure for two machines is 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 programmatically create a report. The user will copy and paste the Word template, Excel template and file structure into his working directory. An Excel worksheet displays information about this test. that is, the number of machines tested. The template will be built for 1 car.

VBA will pull out the number of machines that will be scanned from the Excel template. Then it will index the numbers and tables in the Word file, duplicate them for the specified number of machines in the right place in the Word file and associate them with the correct locations of the source file. If an iteration of the test was done, I would use the code above to update the numbers and tables.

What is the easiest way to fix this? Which method will generate and update table data the fastest? From the reading I made, it seems that it is faster to set up tables for import as images, rather than linking data like this application . I would like the code to be fast, reliable, reliable and not rely on any add-ons such as this . I might need something like this , but that seems a bit redundant.

Any help would be greatly appreciated - I am trying to understand the relationship between Word VBA, field codes and bookmarks and to best use them to my advantage.

Answer 1

This problem is ideal for automation. It seems to me that you should have a basic template and fill out the information largely solely on the basis of an Excel spreadsheet with information about the computer.

Bookmarks in Word are you friend. You can use them to initially post your tables and figures, as well as update them if new information becomes available (although this requires additional effort). I did a little work on importing data from Excel into Word in the form of tables and definitely recommend not importing the tables as images. The size of your file will quickly go outside, and people who want to electronically extract data from tables will want to inflict shock teaspoons on you.

From the information you provided, I probably start my code in Excel using an Excel template as an active workbook. So I installed it:

• Start with the add-in that displays the ribbon tab. This can be used for any excel template for a machine.
• Use OLE automation to open Word and create a new document from a Word template (there is a lot of information on the web for this)
• Read in the document structure from the Excel template and customize the word document. Since you know the layout, you can fill out placeholders (bookmarks) for all figures and tables along with captions.
• Scroll through all the tables and insert them first. The trick to inserting tabular data from Excel into Word is to insert a table as delimited text and then convert it to a table.
• Scroll and paste all the numbers.
• If you encapsulate bookmarked digits and add a bookmark to the tables, you can update them as needed.

Note that none of these options is trivial in itself. If you want to apply additional formatting, for example, bold headings, merged cells or tables that are paginated, then this is much more work.

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

The question is quite wide to provide a lot of code, but for an example you should use the examples and functions below. If you have additional questions, you should ask them a new question.

When you enter a Word document (created from a template and already filled with bookmarks that determine the location of the tables) and the names of all the tables you have, the following functions will populate these tables in 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 in the bookmark and provide a new 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 row containing the data, and then convert it to 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 

Answer 2

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

During development, remember to regularly check that the resulting latex is taken by htlatex or oolatex.

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

Link about oolatex, including image file names: https://groups.google.com/forum/#!topic/comp.text.tex/p--jBb7MIuQ