none
Inlineshapes how resize?

    Question

  • We have word document that contain inlineshape object in Excel worksheets format

    how with vba code resize this inlineshape? I mean resize without stretching visible picture. Scaleheight do not work.


    Sunday, December 09, 2012 9:39 PM

Answers

All replies

  • You have to set ScaleHeight and ScaleWidth to the same number, e.g.

    With ActiveDocument.InlineShapes(1)
        .ScaleHeight = 150
        .ScaleWidth = 150
    End With

    (this is from the Word help), or

        Dim DesiredHeight As Single
        Dim Factor As Single
        DesiredHeight = Application.CentimetersToPoints(5)
        With ActiveDocument.InlineShapes(1)
            Factor = DesiredHeight / .Height * 100
            .ScaleHeight = Factor
            .ScaleWidth = Factor
        End With


    Regards, Hans Vogelaar

    Sunday, December 09, 2012 10:03 PM
  • You have to set ScaleHeight and ScaleWidth to the same number, e.g.

    With ActiveDocument.InlineShapes(1)
        .ScaleHeight = 150
        .ScaleWidth = 150
    End With

    (this is from the Word help), or

        Dim DesiredHeight As Single
        Dim Factor As Single
        DesiredHeight = Application.CentimetersToPoints(5)
        With ActiveDocument.InlineShapes(1)
            Factor = DesiredHeight / .Height * 100
            .ScaleHeight = Factor
            .ScaleWidth = Factor
        End With


    Regards, Hans Vogelaar

    I need resize only the height of the worksheet, it's exist some-thing like above? 

    for example, if I activate inlineshapes manually by double click- I can move bottom border up-down and set it position, how make this action with VBA? To use macrorecorder do not help

    Sunday, December 09, 2012 10:38 PM
  • So you want to increase the number of rows that is displayed? According to Word MVP Cindy Meister, who knows far more about this than I, that is not possible using VBA. See Resize an embedded excel OLE object in Word programattically.


    Regards, Hans Vogelaar

    • Marked as answer by sl-avik Monday, December 10, 2012 1:33 PM
    Sunday, December 09, 2012 11:03 PM


  • Regards, Hans Vogelaar

    Yes, increase the number of rows that is displayed. I create document from .dot file and after that by vba insert some rows in then inlineshapes, but after that I need to display added rows (or resize ole object).

    Thank you  for attention and link,

    the question is open still, it sadly if that impossible using VBA((

    Monday, December 10, 2012 1:31 PM
  • The answer to this is on the Excel side:

    The linked Word object refers to a Name in Excel. That name is usually just a simple range name, but a Name can also refer to a formula that returns a range.

    So for example you could define a name:

    RangeToLink

    which refers to e.g.

    =OFFSET(topofmytable,0,0,RowsToInclude, ColumnsToInclude)

    then your link in Word to RangeToLink will change depending on the rows you specify ( which of course can be a formula or reference too).

    Building on this idea, you can add a function to your workbook:

    Function PIVRange(r As Range)

    Dim pv As PivotTable
    Set pv = r.PivotTable
    Set PIVRange = pv.TableRange1
    End Function

    Now you can create a name e.g.

    MyPivot => =PIVRange(topofmypivottable)

    Now, when you link in Word to the name MyPivot, the linked object in Word will show the whole pivot table, no matter how it changes shape when you refresh it or reconfigure the rows and columns.

    regards

    Brian Lewis

    Tuesday, December 10, 2013 11:17 PM