Ggtcf

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

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.

Simply name the range in Excel before copying & pasting the link into Word. From then on, any changes to the scope or content of the named range in Excel will be reflected in Word.

Alternatively, if you've already established the link, name the range in Excel, then edit the field code in Word. To do this, select the linked range and press Shift-F9. It should now appear as something like:

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

Change "Sheet1!R1C1:R2C2" (including the quotes) to your range name (without the quotes), then press F9 to refresh the link.