Word VBA Create chart on each page

Question

I work for a school district. We need letters to go out to parents with their students absences. Data is already used from an Excel Document to create a mail merge. From Excel document I am pulling 3 fields into a "hidden table" Student name, median Absences and student absences. The hidden table is just a border-less table with white text on each page. (white so it wont print) The table is used to generate a chart using the following code;

Dim tblOne As Table
Dim cht As Chart
Dim chtWkSht As Excel.Worksheet
Dim X As Integer
Dim RowCount As Integer
Dim ColumnCount As Integer
Dim LastColumn As String



For Each tblOne In ActiveDocument.Tables
    tblOne.Range.Copy
   'Create Chart
    Set cht = ActiveDocument.InlineShapes.AddChart.Chart


    Set chtWkSht = cht.ChartData.Workbook.Worksheets(1)   
'Set table size
    RowCount = tblOne.Rows.Count
    ColumnCount = tblOne.Columns.Count
'Determine Spreadsheet column letter for lastColumnTable
    If ColumnCount < 26 Then
        LastColumn = Chr(62 + ColumnCount)
    Else
     LastColumn = Chr(Int(ColumnCount / 26) + 64) & Chr((ColumnCount Mod 26) + 64)
   End If
 'Resize chart data area to table size and paste table data
    With chtWkSht
        .ListObjects("Table1").DataBodyRange.Delete
        .ListObjects("Table1").Resize chtWkSht.Range("A1:" & LastColumn & RowCount)
        .Range("A1:" & LastColumn & RowCount).Select
       .Paste
    End With

    cht.ChartData.Workbook.Close
Next

This does generate a chart for every student but it generates all the charts on the first page. I need the charts to appear on the page corresponding for each student. I'm fairly new to VBA. I am not sure if the best method to do this would be to create the chart in table maybe using a bookmark?

Or update an existing chart on each page with the data on the "hidden table".

In either case I am not sure how to approach it. What I do know is that using the hidden table with the mail merge option has created separate tables with the correct information for each student. This is the reason I went with this method.

Any suggestions/Help would be GREATLY appreciated as the alternative is updating these manually.

Thank you

Answer 1

Formatting the "hidden tables" as white text Word is not the best option. This not only makes editing the document more difficult it also doesn't truly hide the table as Word will still instruct the printer to print it, resulting in a blank area and possibly extra pages being printed. A better option is to change the font properties to hidden. This will make editing easier as the text can be viewed on screen and exclude the table from the printer instructions.

The charts appear on the first page because you have not told Word where you want the chart to be placed. As a result Word simply inserts the charts at the beginning of the document.

You will need to assess your document to find an appropriate place to put the chart. If each letter contains the table with the data for the chart then you could position the chart relative to that, ideally an empty paragraph just before or just after the table.

For Each tblOne In ActiveDocument.Tables
    tblOne.Range.Copy
    'get chart location
    Dim chtRange as Range
    set chtRange = tblOne.Range
    'to place chart before table
    chtRange.Collapse wdCollapseStart
    chtRange.Move wdParagraph, -1
    'to place chart after table
    'chtRange.Collapse wdCollapseEnd
    'chtRange.Move wdParagraph, 1
    'Create Chart
    Set cht = ActiveDocument.InlineShapes.AddChart2(Range:=chtRange).Chart

Edit: Now that I have had chance to test this on my PC I have changed the last line and can confirm that the code works. As I am using Office 365 I have used the AddChart2 method. The deprecated AddChart method works the same way.