Export an Excel Chart to Word

You can use VBA to streamline a lot of your day-to-day practices, eliminate boring tasks, and reduce tedium. On this blog I recently showed how to Export an Excel Chart to PowerPoint. In this article I will show how to Export an Excel Chart to Word. I’ll follow up soon with more intricate routines that accomplish more chart exporting tasks, for both PowerPoint and Word.

There are a number of things you need to understand:

• Excel’s Object Model
• Word’s Object Model
• Early and Late Binding, References, Libraries

We can learn all about Excel’s and Word’s object model by recording macros and examining the code (unfortunately PowerPoint has no macro recorder), as well as using tools like IntelliSense and the Object Browser in the VB Editor. And we have Google at our fingertips. Search on “Excel VBA” and the particular keyword that has you stumped, or type the exact error message into the search box.

We can learn about another application’s object model through VB Editor tools. Word has Charts, Shapes, Ranges, and Tables like Excel does; Word’s charts and shapes behave much like Excel’s, but its ranges and tables are much different. And Word has Documents instead of Workbooks and Worksheets.

I’ve already discussed Early and Late Binding in Export an Excel Chart to PowerPoint: Early and Late Binding. Please refer to that post for more information.

The Example Project

Here is a simple data set and chart in Excel.

Here is a blank document in Word.

We’re going to export that chart from Excel into the cursor position in that Word document.

Early Binding – Setting References

To set a reference to an external library, go to the VB Editor and make sure the project you’re working on is the active project. Go to the Tools menu > References, and the References dialog appears.

This dialog lists each open workbook and add-in, as well as all libraries installed on your system. This includes many that are not appropriate for VBA development, and using these may result in spectacular system failures. But any of them may one day come in handy. For example, long ago I used the WinAmp library (you youngsters probably don’t remember WinAmp) to build a UserForm-driven music utility, in which I could set up playlists and play music files in Excel.

Several references are added by default: Visual Basic for Applications, Microsoft Excel 2016 Object Library, OLE Automation, and Microsoft Office 2016 Object Library; if you add a UserForm to your project, Microsoft Forms 2.0 Object Library is also added automatically. In this view you can see that the Microsoft PowerPoint 16.0 Object Library has already been checked, and just before taking this screenshot I checked the Microsoft Word 16.0 Object Library. If a library is not checked and you haven’t used it recently, you’ll have to scroll down until you find it, then check it.

These library references are only set for the active VB project. Other projects may have different libraries referenced. For example, I used two workbooks while working on this tutorial: one with the PowerPoint and Word libraries checked for Early Binding, the other without for Late Binding.

Outline of Our Procedure

Here is the entire VBA procedure that will export the active Excel chart to Word; it’s a little easier than the corresponding code for PowerPoint. The code is organized like this:

• Several object variables have been declared at the top of the procedure.
  • In Early Binding, these objects are declared as Word objects.
  • In Late Binding, these objects are declared as generic objects.
• We prevent an error if the user has not selected a chart.
• We figure out where to paste the chart.
  • We look first for a running instance of Word.
    • If Word is not running, we start it up.
  • We look for the active document.
    • If there is no active document, we create a new blank document.
  • We define the Word range at the cursor.
• We copy the active chart.
• We paste the chart as a shape object in Word (it’s still a real chart).

Early Binding – The VBA Code

Option Explicit

Sub SimpleActiveChartToWord_EarlyBinding()
  Dim wdApp As Word.Application
  Dim wdDoc As Word.Document
  Dim wdRng As Word.Range
  
  ' bail out if no active chart
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again!", vbExclamation, "Export Chart To Word"
    Exit Sub
  End If
  
  ' get Word application if it's running
  On Error Resume Next
  Set wdApp = GetObject(, "Word.Application")
  On Error Resume Next
  
  If wdApp Is Nothing Then
    ' word not running so start it and create document
    Set wdApp = CreateObject("Word.Application")
    Set wdDoc = wdApp.Documents.Add
  Else
    If wdApp.Documents.Count > 0 Then
      ' get active document
      Set wdDoc = wdApp.ActiveDocument
    Else
      ' no active document so create one
      Set wdDoc = wdApp.Documents.Add
    End If
  End If
  
  ' get cursor location
  Set wdRng = wdDoc.ActiveWindow.Selection.Range
  
  ' copy chart
  ActiveChart.ChartArea.Copy
  
  ' paste chart
  wdRng.Paste
  
End Sub

Late Binding – The VBA Code

Option Explicit

Sub SimpleActiveChartToWord_EarlyBinding()
  Dim wdApp As Object  ' Word.Application
  Dim wdDoc As Object  ' Word.Document
  Dim wdRng As Object  ' Word.Range
  
  ' bail out if no active chart
  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again!", vbExclamation, "Export Chart To Word"
    Exit Sub
  End If
  
  ' get Word application if it's running
  On Error Resume Next
  Set wdApp = GetObject(, "Word.Application")
  On Error Resume Next
  
  If wdApp Is Nothing Then
    ' word not running so start it and create document
    Set wdApp = CreateObject("Word.Application")
    Set wdDoc = wdApp.Documents.Add
  Else
    If wdApp.Documents.Count > 0 Then
      ' get active document
      Set wdDoc = wdApp.ActiveDocument
    Else
      ' no active document so create one
      Set wdDoc = wdApp.Documents.Add
    End If
  End If
  
  ' get cursor location
  Set wdRng = wdDoc.ActiveWindow.Selection.Range
  
  ' copy chart
  ActiveChart.ChartArea.Copy
  
  ' paste chart
  wdRng.Paste
  
End Sub

Bits and Pieces

The Objects

We need to use the Word Application (we use the Excel application all the time in Excel VBA, but usually get away with ignoring it), plus a Word document, and a Word range. The chart is pasted as a Shape. I’ve shown the Early Binding Word object types below; in Late Binding, these are all plain old Objects. Notice that I keep the Early Binding definitions of these objects in comments, to help when I have to figure out the code later; notice also that the variable names are fairly self-explanatory: wdApp = Word Application, etc.

  Dim wdApp As Word.Application
  Dim wdDoc As Word.Document
  Dim wdRng As Word.Range

User-Proof the Code

Here we test that the user has selected the chart. If he has not, we pop up a message reminding him to select one, then try again.

  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again!", vbExclamation, "Export Chart To Word"
    Exit Sub
  End If

Alternatively, we could look for charts on the active worksheet. If there’s only one chart, we can use that one. Or if there are several charts, we could pick the first one, or the one that is closest to the top left corner of the sheet.

In an upcoming post I’ll show how to export all the charts on the active sheet, or just all charts the user has selected.

Get the Word Application

We use GetObject to find the running instance of Word. We wrap it in On Error statements so we don’t get an error if it’s not running. If it’s not running, wdApp is in fact Nothing, so we use CreateObject to start it up.

  On Error Resume Next
  Set wdApp = GetObject(, "Word.Application")
  On Error Resume Next
  
  If wdApp Is Nothing Then
    Set wdApp = CreateObject("Word.Application")

Word supports multiple running instances at a time. If we were constructing an entire report from Word templates and Excel content, we could ignore any running instance of Word, and create a new instance just for our report. But it’s often more useful to paste content in an active document, for example, if you are writing a report in Word side by side with your data in Excel.

At this point, it would be wise to test that we were able to start up Word (which I have not done). If we couldn’t start Word, for example, if Word were not installed on the computer, we would have to abandon the procedure without trying to use the various Word objects later in the code. We should also alert the user so he could rectify the situation.

Get the Active Document in Word

ActiveDocument is probably obvious enough. If there is one, continue; otherwise create one first.

wdDoc.ActiveWindow.Selection.Range: locations in Word are ranges. A range can be a single point, like the cursor insertion point, or a range can be a longer bit of text, like a word, a sentence, or a book chapter. The selection in the active window is like the selection in Excel. It can be a single point (a single cell) or an extended amount of text (collection of cells). This code defines the selection as the place where we will paste out chart.

Again, we should check that we did successfully end up with a Word document and a Word range, to avoid errors later in the code.

Copy, Paste, and Align

Copying is straightforward: we copy the chart’s chart area. We could instead copy the chart’s parent ChartObject, which works exactly the same for an embedded chart, but would crash for a chart sheet.

We use wdRng.Paste to paste the chart as a shape (a chart’s ChartObject is the Excel Shape that contains the chart), at the location of the range in Word.

The Result

Whether we use Early or Late Binding, the result is the same. Below is our formerly blank Word document, now containing our Excel chart.

Summary

This tutorial showed how to use VBA to export an Excel chart to Word. Both Early and Late Binding were demonstrated, and reasons were provided for preferring Late Binding for most cases.

The approaches for exporting an Excel chart to PowerPoint are similar, and were spelled out in Export an Excel Chart to PowerPoint.

Follow-up articles will cover exporting other content (worksheet ranges (“tables”)) to PowerPoint or Word, and exporting multiple charts in one procedure, which will really reduce your daily tedium. These articles will also describe common code errors you may encounter and suggest workarounds.