Need help understanding linked Excel charts in Word

[Rudi]

Hi all,

Please note: I have cross-posted the same question on the Microsoft Community forum, here.

Office Version: Microsoft 365

I'm supporting someone who has a Word document (report) that contains dozens of charts linked to an Excel file. They settled on using "Paste Special > Paste Link > Microsoft Excel Chart Object" which essentially pastes as a picture. This seemed to be the only object to retain formatting when colours are updated in Excel. No other chart seemed to retain formatting?? The charts are pasted into a 2-column table so that it can present charts in a tiled format for comparison; about 6 charts on an A4 page.

Four issues I am finding when managing this Word file with linked charts:

1. The file functioned 100% for a few days. This morning, she opened the file, and pasted two more charts from a different workbook (same paste link as above). After closing and reopening the file, choosing YES to update, all the charts resized, not from the object borders (ie: they still fit into the table cells, but it's as if they zoomed in and only shows a cropped section of the chart int the object borders)?? How does one fix (or prevent this from happening?)

2. I have noticed that if one opens the document and chooses NO to the update prompt, then the charts become dead. In other words, one cannot right click on it and chose Update Link. To reestablish a link and get an updated chart, one has to close the file and reopen and select YES. Why is this? On a technical point I see the LINK syntax changes from this,

{ LINK Excel.Sheet.12 C:\\Users\\rudis\\Desktop\\testS.xlsx "Sheet1![testS.xlsx]Sheet1 Chart 1" \a \p }

--- to this

{ LINK Excel.Sheet.12 "C:\\Users\\rudis\\Desktop\\testS.xlsx!Sheet1![testS.xlsx]Sheet1 Chart 1" "" \a \p }

--- notice how the chart item is move out of the double quotes and now up against the path.

3. This morning, the new pasted links contained a new argument in the link syntax. [ \* MERGEFORMAT ]
I have no idea where this comes from, and am concerned that this affected the sizing of the charts?? What does this argument mean and how did it suddenly appear in the link syntax?

4. Is there any advice or tips you can provide me regarding managing linked chart objects in Word. It seems so volatile and unpredictable. I see that there is not much n the web that I can source to guide me. Your input will be most valued.

Many TX.

[stuck]

This won't help but...

Way back in about 2000 I tried to embed Excel charts in a Word doc. I quickly gave up on the idea, the location of the linked files had to remain the same, the performance hit on opening Word was too great, etc. In the subsequent 22 years I worked with placing charts in Word docs I only ever inserted charts as pictures.

Ken

[Charles Kenyon]

This won't help but...

Way back in about 2000 I tried to embed Excel charts in a Word doc. I quickly gave up on the idea, the location of the linked files had to remain the same, the performance hit on opening Word was too great, etc. In the subsequent 22 years I worked with placing charts in Word docs I only ever inserted charts as pictures.

Ken

Hi Ken,

This does not help Rudi, but Paul Edstein has a very good tutorial on relative addressing in fields.
http://www.msofficeforums.com/word/3872 ... files.html

[Rudi]

Hi guys,

No worries with pirating the post as it does not seem to be going anywhere, not even in the Microsoft Community.

Some feedback based on the latest discussion between the requestor (at my work) and I:

We have decided to scrap the Paste Special > Paste Link > Excel Chart Object and returned to the standard copy chart in Excel, and paste into Word, creating a chart object, not a picture object. The only reason we went the other route was because the copy/paste method did not retain chart formatting from Excel. Now, having returned to it, I devised an acceptable solution by designing chart templates (which are shared in the office apps). The templates would contain the necessary formatting which can simply be reapplied to the linked charts if/when it is necessary. As a matter of fact, the macro that resizes the charts in the tables where they are pasted simply reapplies the template formatting whenever it is run. (I added the VBA code below if it might be useful for others reading this thread!)

Anyways, we will test this format in a new Word template later this week to determine if it is more stable and flexible than the older legacy-style chart picture objects.

Code: Select all

Sub ResizeChartsInActiveTable()
Dim Tbl As Table
Dim iShp As InlineShape
Dim CurrTblID As Integer

CurrTblID = ActiveDocument.Range(0, Selection.Tables(1).Range.End).Tables.Count
Set Tbl = ActiveDocument.Tables(CurrTblID)

    For Each iShp In ActiveDocument.Tables(CurrTblID).Range.InlineShapes
        With iShp
            .LockAspectRatio = False
            .Height = CentimetersToPoints(5)
            .Width = CentimetersToPoints(7.5)
            On Error Resume Next
            If .Chart.ChartType = xlColumnClustered Then
                .Chart.ApplyChartTemplate "Col.crtx"
            ElseIf .Chart.ChartType = xlBarClustered Then
                .Chart.ApplyChartTemplate "Bar.crtx"
            ElseIf .Chart.ChartType = xlLine Then
                .Chart.ApplyChartTemplate "Line.crtx"
            End If
            On Error GoTo 0
        End With
    Next iShp
    
    With Tbl
        .AutoFitBehavior wdAutoFitWindow
        .Rows.HeightRule = wdRowHeightAuto
        If .Columns.Count > 1 Then .Columns.DistributeWidth
        .TopPadding = CentimetersToPoints(0.1)
        .BottomPadding = CentimetersToPoints(0.1)
    End With
    
End Sub