CODEWITHSUNDEEP
excelvbams-wordcopy-paste
tbriggs707
tbriggs707

Reputation: 99

Copy from Excel Paste in Word

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:

enter image description here

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.

Upvotes: 0

Views: 181

Answers (1)

Variatus
Variatus

Reputation: 14383

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].

Upvotes: 0

Related Questions