Creating charts in MS Word using from regular MS Word tables

Question

I'm working on a program that can generate MS Word files containing tables.

I want to design a macro that would take the data in the tables contained in the MS Word file and create a chart from it (bar, pie or line charts).

So far I've been able to:

Select the portion of the table I need for source data,
Insert an inline Excel worksheet into the MS Word file.

However, I'm unable to paste the selected portion of the table into the inline worksheet in order to draw a chart.

How can I do this?

joeschwa joeschwa · Accepted Answer · 2011-10-09T03:15:10

The solution below should work for Word 2007 SP2 and Word 2010. It was based on code found in a fantastic article entitled "Creating Charts with VBA in Word 2010" by Peter Gruenbaum which can be read here:

Creating Charts with VBA in Word 2010

It is important to note that for this VBA code to work properly, it should contain a reference to the Microsoft Excel 14.0 Object Library (for those that do not know how to do this, the article linked to above explains how to add this reference in detail).

Sub MakeChartFromTable()

Dim myTable As Table
Dim salesChart As Chart
Dim chartWorkSheet As Excel.Worksheet
Dim x As Integer
Dim RowCount As Integer
Dim ColumnCount As Integer
Dim LastColumn As String

For Each myTable In ActiveDocument.Tables
    myTable.Range.Copy
    'Create Chart
    Set salesChart = ActiveDocument.Shapes.AddChart.Chart
    Set chartWorkSheet = salesChart.ChartData.Workbook.Worksheets(1)
    'Determine size of table
    RowCount = myTable.Rows.Count
    ColumnCount = myTable.Columns.Count
    'Determine spreadsheet column letter for last column of table
    If ColumnCount < 26 Then
        LastColumn = Chr(64 + 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 chartWorkSheet
        .ListObjects("Table1").DataBodyRange.Delete
        .ListObjects("Table1").Resize chartWorkSheet.Range("A1:" & LastColumn & RowCount)
        .Range("A1:" & LastColumn & RowCount).Select
        .Paste
    End With
    salesChart.ChartData.Workbook.Close
Next

End Sub

This code creates charts from entire Word tables, so incorporating your code for selecting partial tables (as indicated in your question) will be required. Additional code will also be needed to position the charts within the Word document. Hope this helps.

Creating charts in MS Word using from regular MS Word tables

1 Answers