CODEWITHSUNDEEP
excelvbapowerpoint
Andrew
Andrew

Reputation: 6860

Resizing chart in powerpoint after inserting it from excel (Excel VBA)

I have some code that takes a chart in and pastes it into a slide. However, once it is pasted into I'm not sure how to resize the chart. I've looked into finding which number "shape" it is and I can do this manually but I don't know how to automate this. Any help would be appreciated, thanks!

Here is my code:

Sub AddChartToPPT(PPT As PowerPoint.Application, Slide As Integer, Chart As String, FromTop As Double, FromLeft As Double, Length As Double, Width As Double)
Dim activeslide As PowerPoint.Slide
PPT.ActiveWindow.View.GotoSlide Slide
Set activeslide = PPT.ActivePresentation.Slides(Slide)
ActiveSheet.ChartObjects(Chart).Activate
ActiveChart.ChartArea.Copy
activeslide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select
PPT.ActiveWindow.Selection.ShapeRange.Left = FromLeft
PPT.ActiveWindow.Selection.ShapeRange.Top = FromTop
'Need to add scaling code
End Sub

Upvotes: 1

Views: 9362

Answers (2)

Jon Peltier
Jon Peltier

Reputation: 6063

In my experience, it's easier to resize the chart object in Excel prior to pasting it into PowerPoint. First, Excel's object model is clearer. Second, resizing a metafile may result in distortion, while resizing the chart and pasting as a metafile that needs no resizing does not.

Upvotes: 2

nutsch
nutsch

Reputation: 5962

Try this:

dim shp as Object
set shp=activeslide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture)
shp.Left = FromLeft
shp.Top = FromTop

so with the rest of your code:

Sub AddChartToPPT(PPT As PowerPoint.Application, Slide As Integer, Chart As String, FromTop As Double, FromLeft As Double, Length As Double, Width As Double)
Dim activeslide As PowerPoint.Slide, shp as Object
PPT.ActiveWindow.View.GotoSlide Slide
Set activeslide = PPT.ActivePresentation.Slides(Slide)
ActiveSheet.ChartObjects(Chart).Activate
ActiveChart.ChartArea.Copy
set shp=activeslide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture)
shp.Left = FromLeft
shp.Top = FromTop
'Need to add scaling code     
End Sub

Upvotes: 2

Related Questions