Copy from Excel Paste in Word

Question

In Excel, I have about 20 sheets with 20 charts in each that I need to copy/paste into Word documents. One Word doc per Excel sheet. I found this article with a solution that I modified to accept a ChartObject as a parameter so that I don't have to think about which chart is being copied. I'm getting the following run-time error on the last line where it calls PasteSpecial in the CopyChart2Word() function:

Which isn't very helpful because it doesn't tell me what is wrong. But the chart is pasted into the Word document with half of the data points missing.

Code:

Public Function moveCharts()
  Dim i As Integer
  Dim name As String
  Dim ChtObj As ChartObject
  Dim dummy As Variant

  initGlobals
  For i = 0 To UBound(employees)
    name = employees(i)
    For Each ChtObj In Worksheets(name).ChartObjects
        dummy = CopyChart2Word(ChtObj)
    Next ChtObj
  Next i
End Function

Public Function CopyChart2Word(chartObj As ChartObject)
  Dim wd As Object
  Dim ObjDoc As Object
  Dim FilePath As String
  Dim FileName As String
  FilePath = "C:\Users\name\Desktop"
  'Empty document for now
  FileName = "Template.docx"

  'check if template document is open in Word, otherwise open it
  On Error Resume Next
  Set wd = GetObject(, "Word.Application")
  If wd Is Nothing Then
    Set wd = CreateObject("Word.Application")
    Set ObjDoc = wd.Documents.Open(FilePath & "\" & FileName)
  Else
    On Error GoTo notOpen
    Set ObjDoc = wd.Documents(FileName)
    GoTo OpenAlready
    notOpen:
    Set ObjDoc = wd.Documents.Open(FilePath & "\" & FileName)
  End If
  OpenAlready:
  On Error GoTo 0

  'find Bookmark in template doc
  wd.Visible = True
  'ObjDoc.Bookmarks("Bookmark1").Select

  'copy chart from Excel
   chartObj.chart.ChartArea.Copy

   'insert chart to Bookmark in template doc
   'wdPasteMetafilePicture didn't work so I used the numeric value 3
   'wdInLine didn't work so I used the numeric value 0
   wd.Selection.PasteSpecial Link:=False, _
   DataType:=3, _
   Placement:=0, _
   DisplayAsIcon:=False
 End Function

Link to sample chart.

Maybe include which line is highlighted when you get the error and click debug? and fyi, most people are not going to download a file from your google drive. — braX
A minimal reproducible example - contained in the question - would be useful... — Cindy Meister

Variatus Variatus · Accepted Answer · 2020-01-26T10:48:23

I suspect that the error may be caused by different instances of Word being open at the same time. In order to eliminate this possibility I recommend to sort out the way you handle Word and the document. The logic of your code is a little confused. Please try this instead.

On Error Resume Next
Set Wd = GetObject(, "Word.Application")
If Err Then Set Wd = CreateObject("Word.Application")

On Error Resume Next
Set ObjDoc = Wd.Documents(Filename)
If Err Then Set ObjDoc = Wd.Documents.Open(FilePath & "\" & Filename)

On Error GoTo 0

I wonder why you need Wd.Visible = True. It should be visible by default. Perhaps the Window isn't the ActiveWindow, however. In fact, Word might not be the active application. I don't think that matters to the code.

But it should matter greatly to the Selection object. Only the ActiveWindow can have a Selection. Therefore, if you have Excel open and run the code you can't access a Selection object in Word. And, in reverse, if you have Word open and make a selection and then change to Excel, the Selection object would be lost. That might also cause a fatal error. Just follow the rule: "Never Select anything in VBA [until the last line of your code].

Copy from Excel Paste in Word

1 Answers