Copying Editable Excel chart to Word Error 4605

Question

I have a macro that is intended to copy a chart from excel into a word document in the same way as manually copy and pasting using "Keep Source Formatting and Embed Workbook." Below is the code that, to my understanding, should accomplish this.

     Set reductionChart = graphWorksheet.ChartObjects("reduction")
     reductionChart.Copy
     masterReport.Paragraphs.Last.Range.PasteAndFormat wdChart

graphWorksheet is the worksheet that contains the graphs, masterReport is the word document.

The issue I am having is error 4605 command not available on the PasteAndFormat line. I happened to discover that manually copying the graph then running the line worked without issue. Thinking that maybe right clicking copied in a different way than .copy so I record a macro of the action and ended up with:

    ActiveSheet.ChartObjects("Reduction").Activate
    ActiveChart.ChartArea.Copy

Even substituting this in the error still occurs. What is happening here?

After some additional testing I am thinking that possibly when using .copy the chart is sort of only stored within excel and not the clipboard so when paste and format looks for something it see an empty clipboard and has an error, but right clicking copy stores it to the clipboard hence why is available still even after I run ActiveChart.ChartArea.Copy again.

I've attempted to create new workbook with a single sheet and chart. Also tried using late binding instead on the off chance that did something. This is the full code still giving the same issue

Sub test()
    Dim masterWord As Object
    Dim masterReport As Object
    
    Set masterWord = CreateObject("Word.Application")
    Set masterReport = masterWord.Documents.Add
    masterWord.Visible = True
    
    ThisWorkbook.Worksheets(1).ChartObjects("Chart 1").Copy
    masterReport.Paragraphs.last.Range.PasteAndFormat wdChart
End Sub

user_16611259 · Answer

After much testing this is the best workaround I'm managed to figure out.

reductionChart.Chart.ChartArea.Copy
masterReport.Paragraphs.Last.Range.PasteAndFormat wdFormatOriginalFormatting
masterReport.InlineShapes(masterReport.InlineShapes.Count).LinkFormat.BreakLink

wdFormatOriginalFormatting and wdPasteDefault both work and don't seem to make a difference to the outcome as far as I have seen.

This has one issue that I've found which is that on ActiveDocument.Fields.Update an information box will popup warning that the linked file is unavailable. This will occur for each unlinked item. I attempted to use Application.DisplayAlerts = False but this did not prevent the popup. This may come back to bite me, but I simply removed this line as it was unnecessary for my purposes.

Copying Editable Excel chart to Word Error 4605

1 Answers