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.