Export Excel Chart To Word Using PasteFormat is Returning Error 4605 Command Not Available

Question

So I'm trying to understand what is wrong with my code. All I'm doing is taking some charts in my Excel workbook and exporting them to a Word document but I keep getting an error if I try to paste them a certain way. Here's my code:

Sub ExportingToWord_MultiplePages2()

    'Declare Word Variables
    Dim WrdApp As Word.Application
    Dim WrdDoc As Word.Document

    'Declare Excel Variables
    Dim ChrtObj As ChartObject

    'Create a new instance of Word
    Set WrdApp = New Word.Application
        WrdApp.Visible = True

    'Create a new word document
    Set WrdDoc = WrdApp.Documents.Add

    'Loop through the charts on the active sheet
    For Each ChrtObj In ActiveSheet.ChartObjects

        'Copy the chart
        ChrtObj.Chart.ChartArea.Copy

        **'THIS WON'T RETURN AN ERROR**
        With WrdApp.Selection
            .PasteAndFormat Type:=wdChartPicture
        End With

        '**THIS WILL RETURN THE ERROR**
        With WrdApp.Selection
            .PasteAndFormat Type:=wdChartLinked
        End With

        'Clear the Clipboard.
        Application.CutCopyMode = False

    Next ChrtObj

End Sub

This is the weird part because I've provided two different ways to paste, the first one I paste it as a chart picture and that works fine. However, if I try wdChart or wdChartLinked it won't work! I get Error 4605 "Command Not Avaiable".

Any thoughts as to why this would be the case?

I can't reproduce the error - your code worked fine for me. What kind of chart is it that you're trying to paste over? How many charts? There must be something else going on here causing your error. Also, what version of Word and Excel are you using? And what reference libraries? — dwirony
– dwirony, Commented Dec 12, 2018 at 15:08
So I'm only exporting two clustered column charts in this code and I'm using Excel 2016 & Word 2016 on Office 365. Finally, I made a reference to the Microsoft Word 16.0 Object Library in this particular script. The only thing I would say is "Unique" about my charts is that they are using a custom color scheme that I made in Excel. — areed1192
– areed1192, Commented Dec 12, 2018 at 15:19
Two things to try: 1) Copy/paste as a user and try to choose the corresponding option from the paste option button; 2) Copy/paste special as a user and try to link; 3) create a new, very simple minimal workbook and test the code in the question. Reason: something may be "odd" in this particular workbook or with the charts. Interesting to see if Word will let them be pasted with a link, at all, and whether the problem is general or restricted to this workbook. — Cindy Meister
– Cindy Meister, Commented Dec 13, 2018 at 16:38

areed1192 · Accepted Answer · 2018-12-12 15:51:14Z

1

So I found a workaround to the problem, but I'm still not sure why PasteFormat will not work with a linked chart.

If I replace:

'**THIS WILL RETURN THE ERROR**
With WrdApp.Selection
    .PasteAndFormat Type:=wdChartLinked
End With

With the following, I no longer get an error:

'**THIS WILL NOT RETURN AN ERROR**
With WrdApp.Selection
    .PasteSpecial Link:=True, DataType:=wdPasteOLEObject
End With

I guess it has to do something with the format of the chart or something, but I still find it strange that I can paste it as a picture using PasteFormat but not as a linked chart.

edited Dec 12, 2018 at 15:51

answered Dec 12, 2018 at 15:40

areed1192

6026 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dwirony Over a year ago

I figured it was something in your chart - probably some feature that is supported by Excel but not by Word. It's hard to tell without seeing what it was you were trying to bring in.

areed1192 Over a year ago

Yeah I changed it to a simple line chart (the one with default formatting in Excel), just to see if the formatting had something to do with it but it doesn't appear to be the case.

Collectives™ on Stack Overflow

Export Excel Chart To Word Using PasteFormat is Returning Error 4605 Command Not Available

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related