Home>

If i copy the range on Excel with the Copy method and paste it on the PowerPoint sheet with the PasteSpecial method, the size will be corrected without permission after pasting.

Dim ppApp As New PowerPoint.Application
Dim ppPrs As PowerPoint.Presentation
'Open PowerPoint
Set ppPrs = ppApp.Presentations.Open ("test.pptx")'
'Copy the specified range on Excel
ActiveSheet.Range ("B2: G3"). Copy
'Attach to PowerPoint
ppPrs.Slides (1) .Shapes.PasteSpecial DataType: = 0

The height and width can be corrected by specifying the size after pasting, but for some reason only the font size cannot be corrected.

With ppPrs.Slides (1) .Shapes (1)
    .Width = 100'width
    .Height = 200'Height
    .Top = 100'Vertical position
    .Left = 100'horizontal position
    .FontSize = 10'Character size
End With

With this specification, you can specify the width, height, vertical position, and water position, but you cannot specify the font size.

"The object does not support this method."

Will be displayed.

Is it possible to change the font size of the object pasted with PasteSpecial?
Or is it the wrong way to specify it?

vba
  • Answer # 1

    Shape object

    Shape objects do not have a Font property.
    From the TextEffect property, you can get the TextEffectFormat object, and you can change the FontSize etc. there, but it is hard to think that the table is TextFrame etc., so I think that you can also change it with this method.

    Regardless of whether it is before or after pasting, it is recommended to adjust the Font as an Excel object.

  • Answer # 2

    This has solved it.

    Dim ppApp As New PowerPoint.Application
    Dim ppPrs As PowerPoint.Presentation
    Dim TargetTable As Table
    Dim r As Row, c As Cell, tr As TextRange
    'Open PowerPoint
    Set ppPrs = ppApp.Presentations.Open ("test.pptx")'
    'Copy the specified range on Excel
    ActiveSheet.Range ("B2: G34"). Copy
    'Attach to PowerPoint
    ppPrs.Slides (1) .Shapes.PasteSpecial DataType: = 0
    Set TargetTable = ppPrs.Slides (1) .Shapes (1) .Table
    For Each r In TargetTable.Rows
        For Each c In r.Cells
            c.Shape.TextFrame.TextRange.Font.Size = 10'← Set characters here
        Next
        Next