This guide contains:
Start out with the finished product! Add a placeholder chart to your report template.
In Word, navigate to the Insert tab, then click Chart and select the type of chart (bar, column, pie, etc) that you want to add to your reports:
Update the chart so that it looks the way you want! Work on the colors, borders, layout, legend, sizes and more. Don't forget about the data either! Make sure to open up the Excel sheet (right click and select Edit Data > Edit data in Excel) and edit the data with some placeholder labels and data. Make sure to update Cell A1 with a single word (e.g.
Rating), we'll be using this later!
Now that you have your placeholder chart in your report template, we have to make sure we can update that chart with the project-specific data!
The easiest way to do this is usually with IssueCounter controls in a placeholder table that has the same layout as the Excel sheet hidden behind the chart.
In the example above, we have an Excel sheet with 2 columns and 5 rows so we'll create the same size placeholder table:
Finally, make sure that this placeholder table contains a bookmark so that we can reference this placeholder table later with our macro.
In Word, navigate to the Insert tab and click Links > Bookmarks and give it the name
Note, this macro will not work if you are running Word for Mac. Read more about the VBA limitations of Word for Mac in our FAQs.
Add the following macro to your report template:
Sub UpdateChart() Dim dataTable As Table Dim objShape As InlineShape Dim salesChart As Chart Dim chartWorkSheet As Excel.worksheet ' Iterates each inline shape in the active document. For Each objShape In ActiveDocument.InlineShapes ' If the inline shape contains a chart, display the ' data and minimize the application If objShape.HasChart Then Set salesChart = objShape.Chart salesChart.ChartData.Activate salesChart.ChartData.Workbook.Application.WindowState = -4140 Set chartWorkSheet = salesChart.ChartData.Workbook.WorkSheets(1) If chartWorkSheet.Range("A1").FormulaR1C1 = "Rating" Then Call UpdateByRatingChart(chartWorkSheet) End If End If Next End Sub
Make sure to update this line:
If chartWorkSheet.Range("A1").FormulaR1C1 = "Rating" Then as needed so that the macro is looking for the actual value you used cell A1 when you created the chart.
More than one chart in your report?
ElseIf to differentiate between charts:
If chartWorkSheet.Range("A1").FormulaR1C1 = "Rating" Then Call UpdateRatingChart(chartWorkSheet) ElseIf chartWorkSheet.Range("A1").FormulaR1C1 = "Cat" Then Call UpdateCategoryChart(chartWorkSheet) End If
Now, you see how we're calling
UpdateByRatingChart in our previous macro? It's time to define that function next! In the same macro, add the following lines:
Private Sub UpdateByRatingChart(worksheet As Excel.worksheet) Dim tblData As Table Dim intR As Integer Set tblData = ActiveDocument.GoTo(What:=wdGoToBookmark, Name:="tblData").Tables(1) For intR = 2 to 5 worksheet.Range("B" & intR).FormulaR1C1 = CleanCellText(tblData.Cell(intR, 2)) Next intR ActiveDocument.GoTo(What:=wdGoToBookmark, Name:="tblData").Tables(1).Delete End Sub Private Function CleanCellText(objCell As Cell) Dim strCellText As String strCellText = objCell.Range.Text CleanCellText = Trim(Left(strCellText, Len(strCellText) - 2)) End Function
Notice that we're using that
tblData bookmark heavily here so that the macro knows which table to pull the chart data from. We're also including a separate function to clean out the cell text.
Don't want to delete the placeholder table?
Remove this line or comment it out with a
Lastly, since we're setting referencing the Excel worksheets in our macros, we need to include the Excel library. In the macro edit view, click on Tools > References. A window should appear and enable the entry: Microsoft Excel X.Y Object Library. Click OK to save your changes.
Once you have all of the pieces in place, export a sample report and test out your macro.
As always, the Dradis support team is here to help. Contact us if you're having trouble with a report template and we can help!
Next help article: Node content controls →