Thread: Macro to insert 2 graphs from seperate excel tabs into a single word document

Hello everyone,

Am hoping to create a macro that will cut 2 excel graphs (each on their own tab) and paste them into a word document of my choosing. It will alwasy be the first 2 graphs that need replacing in the word document.

I want to do this because I am streamlining a report writing process which is repetitive.

Your help is much appreciated,

rpt21

Put this macro in Excel (2003) where you have your graphs and try it... I hope it can help you:

Sub Macro1()
   Dim wdObj As Object
   Dim sh As Worksheet
   Dim fileToOpen
   Dim myCounter As Long
   
   Set wdObj = CreateObject("word.application")
   'wdObj.Visible = True   'to view word application
   
   'ask what is word document with graphs
   fileToOpen = Application.GetOpenFilename("Word documents (*.doc), *.doc", , _
                "Select word document containing graphs")
   If fileToOpen = False Then
      MsgBox ("No files selected")
      Exit Sub
   End If
   
   'open word document
   wdObj.Documents.Open fileToOpen
   
   'copy chart from Excel
   For Each sh In ThisWorkbook.Sheets
      'sh.ChartObjects(1).Copy
      sh.Activate
      sh.ChartObjects(1).Select
   
      ActiveChart.CopyPicture Appearance:=xlScreen, _
          Size:=xlScreen, Format:=xlPicture
      
      myCounter = myCounter + 1
      wdObj.activedocument.inlineshapes(myCounter).Select
      wdObj.Selection.Paste
      
      DoEvents
   Next sh
   
   'close and save word document
   wdObj.ActiveWindow.Close True
   
   'close word application
   On Error Resume Next
   wdObj.Quit
   Set wdObj = Nothing
End Sub

Regards,
Antonio

Hi Antonio,

Thanks for that. The first part of the code works, it seems to get stuck at "sh.ChartObjects(1).Select". It says "Run-time error '1004': Method 'ChartObjects' of object '_Worksheet' failed".

The first graph is on the 3rd Sheet named 'Leaks graph' and the second graph is on the 4th Sheet named 'Leaks Chronicle' and they are to replace the first 2 graphs in the document of my choice.

I changed .doc to docx because I work with word 2007, hope this doesn't affect anything?

much appreciated,

Ross