Pasting multiple linked Excel Charts to Word returning Run-Time Error 5345 Word Cannot Obtain the Data

Question

I am trying to copy multiple Excel charts and paste them to a Word document, on separate pages, as the data type linked OLEObject but I am getting a run-time error.

Run-time error '5343':

Word cannot obtain the data for the {00020832-0000-0000-C000-000000000046 link.

This is code that I've used in the past but literally, the only thing I changed in this code is to add an outer loop that processes the worksheets in the active workbook. Since adding that outer loop it no longer works, which is a little strange to me because I don't really see what is different.

It works for the first sheet (the currently active one), but fails when the loop moves to the next sheet. It does not matter whether the chart is pasted with or without a link.

Here is the full code for your reference:

Sub ExportingToWord_MultipleCharts()

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

'Declare Excel Variables
Dim ChrtObj As ChartObject
Dim Rng As Range

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

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

'Loop through each worksheet in the active workbook.
For Each WrkSht In ActiveWorkbook.Worksheets

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

        'Copy the chart
        ChrtObj.Chart.ChartArea.Copy

        'Paste the Chart in the Word Document
        With WrdApp.Selection
            .PasteSpecial Link:=True, DataType:=wdPasteOLEObject, Placement:=wdInLine
        End With

        'Add a new page to the document.
        WrdApp.ActiveDocument.Sections.Add

        'Go to the newly created page.
        WrdApp.Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext

    Next ChrtObj

Next WrkSht

End Sub

It returns the error on the following line:

'Paste the Chart in the Word Document
With WrdApp.Selection
     .PasteSpecial Link:=True, DataType:=wdPasteOLEObject, Placement:=wdInLine
End With

If you put in a break-point, so that the macro pauses, can you activate (be currenlty on) the next sheet when the one has finished processing? And does the error then not occur for that second sheet? — Cindy Meister
– Cindy Meister, Commented Dec 13, 2018 at 19:22

areed1192 · Accepted Answer · 2018-12-13 19:35:11Z

1

I found a workaround, but it still doesn't explain why the error is happening. What I had to do is activate the actual worksheet in the loop.

'***ACTIVATE THE WORKSHEET IN ORDER TO REMOVE THE ERROR***
 WrkSht.Activate

For whatever reason, this seemed to remove the error from popping up. However, I find this strange because when I've exported charts from PowerPoint I am not required to activate the worksheet in order to copy it. Here is the code with the adjustments, I've called out the section I added.

Sub ExportingToWord_MultipleCharts()

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

    'Declare Excel Variables
    Dim ChrtObj As ChartObject
    Dim Rng As Range

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

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

    'Loop through each worksheet in the active workbook.
    For Each WrkSht In ActiveWorkbook.Worksheets

        '***ACTIVATE THE WORKSHEET IN ORDER TO REMOVE THE ERROR***
         WrkSht.Activate

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

            'Copy the chart
            ChrtObj.Chart.ChartArea.Copy

            'Paste the Chart in the Word Document
            With WrdApp.Selection
                .PasteSpecial Link:=False, DataType:=wdPasteOLEObject, Placement:=wdInLine
            End With

            'Add a new page to the document.
            WrdApp.ActiveDocument.Sections.Add

            'Go to the newly created page.
            WrdApp.Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext

        Next ChrtObj

    Next WrkSht

End Sub

answered Dec 13, 2018 at 19:35

areed1192

6026 silver badges16 bronze badges

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

4 Comments

Cindy Meister Over a year ago

Yes, that's the thought I had when I asked my last question in comments. FWIW I suspect it might have something to do with how Excel handles copy commands. You know the "marching ants" around the selection/range being copied? And how that can disappear (break the copy/paste) if certain actions are performed in the UI? I'm thinking it has something to do with that - but just a guess on my part.

areed1192 Over a year ago

Thanks for the help Cindy, it is very strange just how volatile this whole process of copying can be. I tried it on my personal laptop, there were no issues but I did it on my work laptop and all of the sudden errors popping up everywhere.

Cindy Meister Over a year ago

Perhaps also a question of processing speed (some async in there, somewhere?) Less volatile would probably be to work with the Open XML file formats on closed files. Not really ideal for VBA, though, and a steep learning curve...

Jon Peltier Over a year ago

The problem is that these inter-application operations don't work as smoothly in recent versions of Office as they did in days of yore. I find myself needing either DoEvents before and after statements that work with objects (charts, shapes, documents, applications), or even extracting them into functions. Forcing VBA to go to another function makes sure that everything is finished in sequence, otherwise, for example, the copy may not be ready for the Paste command.

Collectives™ on Stack Overflow

Pasting multiple linked Excel Charts to Word returning Run-Time Error 5345 Word Cannot Obtain the Data

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related