Search code examples
excelms-wordword-field

How to resize a graphic object in the LINK field?


After a Paste special linking of a range of cells from Excel to Word (2013) the field looks like this:

{ LINK Excel.SheetMacroEnabled.12 D:\\20181228\\SC.xlsm Sheet1!R10C1:R10C20" \a \p }

If you click on the object with the right button, select "Format object" and then click on "?", the Format AutoShape reference article opens.

However, ActiveDocument.Shapes.SelectAll does not detect this object.

This code also does not work, although the error message says that this component is available for pictures and OLE objects:

With ActiveDocument.Shapes(1).PictureFormat 
 .ColorType = msoPictureGrayScale 
 .CropBottom = 18 
End With

What is this object? I cannot find it in Object model (Word).

How to access it through VBA? I want to programmatically resize a group of such objects to 90% of the original.

Upd. @Cindy Meister suggested where to dig, thanks. I wrote the code, it seems to work fine:

Sub ResizeImages()
Dim img As Long
With ActiveDocument
For img = 1 To .InlineShapes.Count
    With .InlineShapes(img)
        .ScaleHeight = 90
        .ScaleWidth = 90
    End With
Next img
End With
End Sub

Solution

  • A Link field must be an InlineShape - it can't be a Shape, not if you can display the field using Alt+F9. Since Shape objects have text wrap formatting any field codes associated with them (usually none) aren't accessible.

    Therefore, any object that's displayed via a Link field should be available via the InlineShape object model.

    For example, the following code loops the fields in the document and, if they're link fields with an Excel source and contain an InlineShape, the InlineShape's dimensions are scaled:

    Dim fld as Word.Field
    For Each fld In ActiveDocument.Fields
        If fld.Type = wdFieldLink
          If fld.Result.InlineShapes.Count > 1 And _
             InStr(fld.OLEFormat.ClassType, "Excel") Then
               Set ils = fld.Result.InlineShapes(1)
               ils.ScaleWidth = 90
               ils.ScaleHeight = 90
          End If
        End If
    Next