Convert an Excel Dashboard to a Word Report Automatically (VBA)

You have worked to generate an Excel report comprised of several charts. However, your audience wants it in a Word format. Can you get this export done, with five minutes to spare? Of course, you can!

The tutorial below shows you how to set up a macro that will automate the conversion of an Excel dashboard to a Word Report. When triggered, the macro will:

• create a new Word document;
• create a table in the Word document;
• copy all the charts in the dashboard and paste them, one after the other, each in a new row in the table in the Word document.

This macro will also ensure the table has a column for paragraph summaries or short highlights on each of the imported charts.

Before we go into the tutorial proper, here are a few assumptions underlying this hack:

• The dashboard has been created with Excel charts alone (which include pie charts, bar charts, line charts, etc.).
• This Word report is to be optimized for digital transfer, so the charts will be exported as non-editable pictures.
• This hack exports only charts, no text, with the expectation that text or explanatory paragraphs will be included after the dashboard has been exported to Word.

Step 1: Name the cell (or range of cells) behind each chart as a named range. Named ranges can be set up either by going to the Name Manager (Formulas > Name Manager, or keyboard shortcut Ctrl+F3) to define a new name, or selecting the cell/range of cells and typing the name in the Name Box (the entry field right beside the Formula Bar). The GIF below demonstrates both methods.

Step 2: Once all Chart ranges have been named (4 charts in our example - Chart1, Chart2, Chart3 and Chart4), access the Visual Basic Editor, and create a new Module (Developer > Visual Basic Editor > Insert > Module).

Step 3: Insert the following code in the newly created module. I have split the code into blocks and placed numbered comments above each block to describe what each block code is accomplishing

<code><pre>
Sub ExporttoDoc()

'1. Declare variables to hold the Microsoft Word Application, Microsoft Word Document, range and table in MS Word and a numerical value
Dim wrdApp As Object
Dim wrdDoc As Object
Dim wrdRange As Word.Range
Dim WordTable As Word.Table
Dim i As Long

'2. Assign the first 3 variables above to a new instance of MS Word, a new Document, and the content (range) of the new document
Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Add
Set wrdRange = wrdDoc.Range(0, 0)

'3. Create a table (with 5 rows, 1 column) in the new Word document and assign it to the table variable created
wrdDoc.Tables.Add wrdRange, 5, 1
Set WordTable = wrdDoc.Tables(1)

'4. Format the table (I focused primarily on the inner and outer border styles, other formatting can be applied as well)
WordTable.Borders.Enable = True
WordTable.Borders.InsideLineStyle = wdLineStyleDot
WordTable.Borders.InsideLineWidth = wdLineWidth075pt
WordTable.Borders.OutsideLineWidth = wdLineWidth150pt
WordTable.Borders.OutsideColor = wdColorGray125

'5. Give the table a heading, on the first row
WordTable.cell(1, 1).Range.Text = "Word Report"
WordTable.cell(1, 1).Range.Bold = 1
WordTable.cell(1, 1).Range.Font.Size = "36"

'6. Split the remaining 4 rows to create a new column (for the chart summaries)
For i = 2 To 5
WordTable.cell(i, 1).Split 1, 2
WordTable.cell(i, 2).Width = WordTable.cell(i, 1).Width / 2
WordTable.cell(i, 1).Width = WordTable.cell(i, 2).Width * 3
Next I

'7. Select each Excel chart - using the named ranges set up in Step 1 to call the charts - copy the charts, and paste them into a new row in the Word document
ThisWorkbook.Worksheets("Excel dashboard").Range("Chart1").Select
Selection.Copy
WordTable.cell(2, 1).Select
wrdApp.Selection.PasteSpecial Link:=False, _ DataType:=wdPasteEnhancedMetafile, _
Placement:=wdInLine, DisplayAsIcon:=False

ThisWorkbook.Worksheets("Excel dashboard").Range("Chart2").Select
Selection.Copy
WordTable.cell(3, 1).Select
wrdApp.Selection.PasteSpecial Link:=False, _ DataType:=wdPasteEnhancedMetafile, _
Placement:=wdInLine, DisplayAsIcon:=False

ThisWorkbook.Worksheets("Excel dashboard").Range("Chart3").Select
Selection.Copy
WordTable.cell(4, 1).Select
wrdApp.Selection.PasteSpecial Link:=False, _
DataType:=wdPasteEnhancedMetafile, _
Placement:=wdInLine, DisplayAsIcon:=False

ThisWorkbook.Worksheets("Excel dashboard").Range("Chart4").Select
Selection.Copy
WordTable.cell(5, 1).Select
wrdApp.Selection.PasteSpecial Link:=False, _
DataType:=wdPasteEnhancedMetafile, _
Placement:=wdInLine, DisplayAsIcon:=False

Application.CutCopyMode = False

'8. Confirm export has been completed and show the Word Report
MsgBox ("Conversion complete!")
wrdApp.Visible = True

End Sub</code></pre>

Because this macro will have to call an application outside MS Excel, the reference library for that application (MS Word) must be selected. To do that, while still in the Visual Basic Editor pane, go to Tools, click on References, and ensure Microsoft Word 16.0 Object Library is selected.

Step 4: You can run the macro now. When you do so (by clicking Developer > Macros, or keyboard shortcut Alt + F8), the Excel dashboard will be exported to Word.