Macro to copy excel chart to specific locations in Word doc

[edwardj3]

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

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

 

[Macropod]

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.

 

[edwardj3]

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

 

[Macropod]

For that, you might declare:
Dim wdIshp As Word.InlineShape
then, when inserting the chart:

  Set wdIshp = wdRng.PasteSpecial(Link:=False, _
    DataType:=wdPasteEnhancedMetafile, _
    Placement:=wdInLine, DisplayAsIcon:=False)
  With wdIshp
    .LockAspectRatio = True
    .Height = wdApp.InchesToPoints(1.25)
  End With

 

[edwardj3]

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?

 

[Macropod]

That suggests an error in implementing the code changes.