Macro to copy excel chart to specific locations in Word doc

edwardj3

New Member
Joined
Jan 16, 2018
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Hi all,

Hope you can provide some advice. I've found and slightly adapted VBA code which requires the user to select a chart in excel, the code then copies and pastes into the location where the cursor is located in the word doc. See code below.

Is it possible for the code to select each chart in the excel document and paste into specific locations in the word document. I think I probably need to reference the chart(s) in each sheet by name e.g. Chart 1 in peak_trans_per_day, then do the select and copy bit and then paste into Word possible using a bookmark e.g. Chart 1 of sheet 1 go to location on page 1 of word doc, chart 2 of sheet 1 go to another location on page 1, chart 3 of sheet 1 to page 3 of word doc etc. I have no idea how the code below could be updated or if you could provide alternative code to achieve what I need.

Thanks

Code:
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.PasteSpecial _
    Link:=False, _
    DataType:=wdPasteEnhancedMetafile, _
    Placement:=wdInLine, _
    DisplayAsIcon:=False
  
End Sub
 
Last edited by a moderator:

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Instead of using:
Set wdRng = wdDoc.ActiveWindow.Selection.Range
you need to specify where in the document (e.g. a bookmark) the chart is to go for a given instance. For example:
Set wdRng = wdDoc.Bookmarks("Bookmark1").Range

Similarly, you need to change:
ActiveChart.ChartArea.Copy
to reference the particular chart you want to copy. For example:
ActiveSheet.ChartObjects(1).Copy

In each case, you don't need to select anything.
 
Last edited:
Upvote 0
Thanks Macropod. I slightly changed the copy bit as it wasn't working to the code below. Do you know how I would change the size of the chart after copying?



Set wdRng = wdDoc.Bookmarks("Bookmark1").Range

' copy chart
Sheets("??").ChartObjects("Chart 2").Chart.ChartArea.Copy

' paste chart
wdRng.PasteSpecial _
Link:=False, _
DataType:=wdPasteEnhancedMetafile, _
Placement:=wdInLine, _
DisplayAsIcon:=False
 
Upvote 0
For that, you might declare:
Dim wdIshp As Word.InlineShape
then, when inserting the chart:
Code:
  Set wdIshp = wdRng.PasteSpecial(Link:=False, _
    DataType:=wdPasteEnhancedMetafile, _
    Placement:=wdInLine, DisplayAsIcon:=False)
  With wdIshp
    .LockAspectRatio = True
    .Height = wdApp.InchesToPoints(1.25)
  End With
 
Upvote 0
Hi Paul.
Declared Dim wdIshp as Word.InlineShape and pasted in your code but it returns Compile Error: Expected Function or variable. .PasteSpecial is highlighted. Any suggestions on how to rectify?
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top