I am working on a VBA macro in Microsoft Word that is supposed to generate a histogram ( Clustered Column ) from data in a table within the document Word.
How can I modify the macro to have multiple charts within the same category, as shown in the image below, using the following table?
I have used the following code to accomplish this task:
Sub CreateWordChart()
Dim oChart As Chart, oTable As Table
Dim oSheet As Excel.Worksheet
Dim RowCnt As Long, ColCnt As Long
Application.ScreenUpdating = False
' get the first table in Doc
Set oTable = ActiveDocument.Tables(1) ' modify as needed
Set oChart = ActiveDocument.Shapes.AddChart.Chart
Set oSheet = oChart.ChartData.Workbook.Worksheets(1)
' get the size of Word table
RowCnt = oTable.Rows.Count
ColCnt = oTable.Columns.Count
With oSheet.ListObjects("Table1")
' remove content
.DataBodyRange.Delete
' resize Table1
.Resize oSheet.Range("A1").Resize(RowCnt, ColCnt)
' copy Word table to Excel table
oTable.Range.Copy
.Range.Select
.Parent.Paste
End With
oChart.PlotBy = xlRows
oChart.ChartData.Workbook.Close
Application.ScreenUpdating = True
End Sub
Any help would be greatly appreciated. Thanks in advance!