Expandable Excel Table to Word Document

Question

I need to link my Excel table in a Word document, but the option which is already in Word can only autorefresh a fix table size e.g. if I copy a 4x4 Table from Excel and paste it with link into Word it will stay 4x4. Only these cells can be refreshed, if I add a column or a row in Excel it doesn't appear in Word.

I hope you got my problem :D

Double click on the table in Word. Then use the anchor points to expand (or contract it) as needed — cybernetic.nomad

Cindy Meister Cindy Meister · Accepted Answer · 2019-03-06T19:46:06

As a user, it's possible to double-click on the Excel object to activate it, then drag the frame to expand/contract it in order to show a different number of rows and/or columns.

There is no equivalent for this in the Word object model - it can't be done using code. Unless...

The table has been inserted with a link and a Link field manages the link. In this case, it's possible to change the cell range specified in the Link field and the embedded object will resize when the field is updated.

For example, if the Link field code is for the range A1:B2

{ LINK Excel.Sheet.12 C:\\Test\\TestMergeData.xlsx Sheet1!R1C1:R2C2 \a \p }

changing it to A1:C3 (R1C1:R3:C3) will display three rows and columns instead of two. (You can view and edit field codes by pressing Alt+F9.)

The VBA code would look like this:

Sub ChangeRangeLinkedExcel()
    Dim fld As Word.Field
    Dim ils As Word.InlineShape
    Dim sFldCode As String
    Dim posRangeStart As Long
    Dim sLinkLeft As String, sRange As String, sLinkRight As String

    For Each ils In ActiveDocument.InlineShapes
        If ils.Type = 2 Then ' wdInlineShapeLinkedOLEObject
            Set fld = ils.Range.Fields(1)
            sFldCode = fld.code
            posRangeStart = InStr(sFldCode, "!R")
            sLinkLeft = Left(sFldCode, posRangeStart)
            sRange = Mid(sFldCode, posRangeStart + 1, 9)
            sLinkRight = Right(sFldCode, Len(sFldCode) - Len(sLinkLeft) - Len(sRange))
            sRange = "R1C1:R3C3"
            fld.code.Text = sLinkLeft & sRange & sLinkRight
            fld.Update
        End If
    Next
End Sub

If the Excel object is not a Link field then the only possibility I've ever found to change what it displays using code is to delete the object, then recreate it. When the new embedded object is insert it will automatically display the specified number of rows and columns.

Expandable Excel Table to Word Document

2 Answers