Resizing Excel Chart Using VBA in Powerpoint

Question

I know this question has been asked before but as I am no VBA expert, I can't seem to get any of the solutions to work for my particular scenario so I was hoping someone could help.

I have some VBA code that copies a selection from Excel, opens a new powerpoint presentation based on a template and then pastes the data into slide two of powerpoint.

The only thing I want to do now is manipulate the size and position of the chart once it is there. What do I need to add to this code below to make that happen?

   'Opens a new PowerPoint presentation based on template and pastes data into Slide 2 of Powerpoint from Excel

Dim PPapp As PowerPoint.Application, PPpres As PowerPoint.Presentation, PPslide As PowerPoint.Slide
Dim XLws As Worksheet

    Set XLws = ActiveSheet
    Set PPapp = New PowerPoint.Application
    Set PPpres = PPapp.Presentations.Open("C:\Users\Colin\Dropbox (Edge45)\Edge45 Team Folder\Edge45 Company Documents\Templates\Powerpoint Templates\Edge45 Audit Template Macro.potm", Untitled:=msoTrue)
    PPapp.Visible = True
    Set PPslide = PPpres.Slides(2)
    XLws.Range("A1:D16").Copy
    PPslide.Shapes.PasteSpecial DataType:=ppPasteHTML, Link:=msoFalse
    Application.CutCopyMode = False

R3uK · Accepted Answer · 2017-01-25 16:16:27Z

2

Define a Shape object and play around :

'Opens a new PowerPoint presentation based on template and pastes data into Slide 2 of Powerpoint from Excel

Dim PPapp As PowerPoint.Application, PPpres As PowerPoint.Presentation, PPslide As PowerPoint.Slide, PPShape As Object
Dim XLws As Worksheet

Set XLws = ActiveSheet
Set PPapp = New PowerPoint.Application
Set PPpres = PPapp.Presentations.Open("C:\Users\Colin\Dropbox (Edge45)\Edge45 Team Folder\Edge45 Company Documents\Templates\Powerpoint Templates\Edge45 Audit Template Macro.potm", Untitled:=msoTrue)
PPapp.Visible = True
Set PPslide = PPpres.Slides(2)
XLws.Range("A1:D16").Copy
Set PPShape = PPslide.Shapes.PasteSpecial(DataType:=ppPasteHTML, Link:=msoFalse)
Application.CutCopyMode = False

With PPShape
    .Top = 10
    .Height = 100
    .Left = 10
    .Width = 100
End With

edited Jan 25, 2017 at 16:16

answered Jan 25, 2017 at 9:27

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

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

7 Comments

Superhans Over a year ago

Thanks for this but I get a run time time error type 13 mis match whatever that is with your amended code - Unfortunately I have no idea what that means - Any ideas?

R3uK Over a year ago

@superhans : Try the edit, I don't know why but apparently PowerPoint changes the type when it's pasted!

R3uK Over a year ago

@superhans : I changed the code in my answer to (I hope !) solve the issue!

Jared Thirsk Over a year ago

If the sheet is long, can Powerpoint show it with a vertical scrollbar?

R3uK Over a year ago

@JaredThirsk : I'd say no... The only thing I found, might be (inside With PPShape): .AutoShapeType = msoShapeVerticalScroll (If not recognized in Excel => Const msoShapeVerticalScroll = 101 (&H65)) Let me know how it goes !

|

Collectives™ on Stack Overflow

Resizing Excel Chart Using VBA in Powerpoint

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related