Resizing a excel pasted object in powerpoint with vba

Question

I've cobbled together a VBA script (I'm no expert, but thanks to the kind folks around here, I've been able to get something together and mostly working) to copy from multiple excel sheets into a powerpoint file (used a template, as you will see from the code.

Sub ATestPPTReport()

Dim PPApp As PowerPoint.Application
Dim PPSlide As PowerPoint.Slide
Dim PPPres As PowerPoint.Presentation
Set PPApp = CreateObject("Powerpoint.Application")
Dim SlideNum As Integer
Dim PPShape As PowerPoint.Shape

Set XLApp = GetObject(, "Excel.Application")

''define input Powerpoint template
    Dim strPresPath As String, strExcelFilePath As String, strNewPresPath As String
''# Change "strPresPath" with full path of the Powerpoint template
    strPresPath = "C:\template.ppt"
''# Change "strNewPresPath" to where you want to save the new Presentation to be created
    strNewPresPath = "C:\macro_output-" & Format(Date, "dd-mmm-yyyy") & ".ppt"
    Set PPPres = PPApp.Presentations.Open(strPresPath)
    PPPres.Application.Activate


PPApp.Visible = True
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''define destination slide
    SlideNum = 1
    PPPres.Slides(SlideNum).Select
    Set PPShape = PPPres.Slides(SlideNum).Shapes("slide1box")
    Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)

''define source sheet
    Sheets("Info1").Activate
'copy/paste from
    XLApp.Range("Info1Block").Copy
    PPSlide.Shapes.PasteSpecial DataType:=ppPasteOLEObject, Link:=msoFalse
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''define destination slide
    SlideNum = 2
    PPPres.Slides(SlideNum).Select
'    Set PPShape = PPPres.Slides(SlideNum).Shapes("slide2box")
    Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)

''define source sheet
    Sheets("Info2").Activate
'copy/paste from
    XLApp.Range("Info2Block").Copy
    PPSlide.Shapes.PasteSpecial DataType:=ppPasteOLEObject, Link:=msoFalse
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Close presentation
    PPPres.SaveAs strNewPresPath
    'PPPres.Close
    'Quit PowerPoint
'PPApp.Quit
'    MsgBox "Presentation Created", vbOKOnly + vbInformation

' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
End Sub

My problem is: how do I resize/reposition the object once it's been pasted?

Vikas · Accepted Answer · 2013-07-25 16:31:52Z

2

The function "PasteSpecial" returns a shape object, which you can use to resize or reposition.

For example:

Dim ppShape as PowerPoint.Shape
set ppShape = PPSlide.Shapes.PasteSpecial(DataType:=ppPasteOLEObject, Link:=msoFalse)

Then you can use this shape object to resize it. For example:

ppShape.Height = xyz
ppShape.Top = abc

etc etc.

Hope this helps. Vikas B

answered Jul 25, 2013 at 16:31

Vikas

8151 gold badge7 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ben.ratcliffe Over a year ago

Thanks for the help, and this is very likely my own inexperience, but it returns a runtime 13 error (type mismatch) with the set ppShape = PPSlide.Shapes.PasteSpecial(DataType:=ppPasteOLEObject, Link:=msoFalse) line. Thoughts?

Vikas Over a year ago

Can you confirm if you declare your variable as Dim ppShape as PowerPoint.Shape and not as Dim ppShape as Shape? If it still gives the error, try to declare the variable as Dim ppShape as Object to avoid the type mismatch error.

ben.ratcliffe Over a year ago

Vikas! Thanks so much! I changed dim ppShape to an object and i was able to get it to work! Many thanks!

Steve Rindsberg Over a year ago

FWIW, while defining the var as an object works, you lose the benefit of intellisense, and really, it's just papering over the basic problem which is that PasteSpecial returns a ShapeRange, not a Shape, hence the Type Mismatch error. Use .PasteSpecial(parameters)(1) instead. That returns the first item in the newly pasted shaperange, which will be a single shape; no error, and you can work with the shape AS a shape.

Jim C · Accepted Answer · 2015-06-05 16:33:10Z

0

This has been working for me:

Set shp = myPresentation.Slides(x).Shapes.PasteSpecial(DataType:=2)
shp.Left = topLeft + 1
shp.Top = midTop + 1
shp.Width = midLeft - topLeft - 1

Note the variables are set locally to place the image where I want it in relation to the slide. You can easily replace with integers.

It also works for DataType:=10 items as well

answered Jun 5, 2015 at 16:33

Jim C

13 bronze badges

Collectives™ on Stack Overflow

Resizing a excel pasted object in powerpoint with vba

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related