Positioning Excel multiple charts on multiple PPT slides

0
votes

This is my first time asking a question here, as you guys and gals are so good that I've never had to until now!

I have the following VBA code, which successfully pulls charts from a spreadsheet, and pastes them onto two newly created PPT slides. The only problem however, is that this code ONLY aligns the chart on the second slide, and doesn't affect the chart on the first slide. I can't for the life of me figure out what's going on her, and would greatly appreciate any input!

Option Explicit
Sub MakeSlides()
Dim myData As Excel.Range
Dim sheet2 As Excel.Worksheet
Dim objPPT As Object
Set sheet2 = ActiveWorkbook.Sheets("Sheet2")
Set myData = sheet2.Range("A2:B43")
Set objPPT = CreateObject("Powerpoint.application")
myData.Copy
Dim pptApp As New PowerPoint.Application
pptApp.Visible = True
Dim pres As PowerPoint.Presentation
Set pres = pptApp.Presentations.Add
Dim firstslide As PowerPoint.Slide
Set firstslide = pres.Slides.Add(1, PowerPoint.PpSlideLayout.ppLayoutBlank)
Dim myChart As Excel.ChartObject
Set myChart = Sheet1.ChartObjects(1)
myChart.Copy
firstslide.Shapes.Paste.Select
' Align pasted chart
pptApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
pptApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
Set sheet2 = ActiveWorkbook.Sheets("Sheet2")
Set myData = sheet2.Range("A45:B69")
myData.Copy
pptApp.Visible = True
Dim secondslide As PowerPoint.Slide
Set secondslide = pres.Slides.Add(1, PowerPoint.PpSlideLayout.ppLayoutBlank)
Set myChart = Sheet1.ChartObjects(2)
myChart.Copy
secondslide.Shapes.Paste
' Align pasted chart
pptApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
pptApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

End Sub

2
excelvbapowerpoint
I know its a bit late but I just wanted to know when you paste an Excel chart on a PowerPoint slide using PasteSpecial does it also copies the methods and events of Excel.Chart on PowerPoint or just the chart? – Zunair Zubair

2 Answers

0
votes

Maybe like this; aligning the chart on the first slide right after you paste it in:

Option Explicit
Sub MakeSlides()
[...]
    myChart.Copy
    firstslide.Shapes.Paste.Select
    ' Align pasted chart
    pptApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    pptApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

    Set sheet2 = ActiveWorkbook.Sheets("Sheet2")
    Set myData = sheet2.Range("A45:B69")
    myData.Copy
    pptApp.Visible = True
    Dim secondslide As PowerPoint.Slide
    Set secondslide = pres.Slides.Add(1, PowerPoint.PpSlideLayout.ppLayoutBlank)
    Set myChart = Sheet1.ChartObjects(2)
    myChart.Copy
    secondslide.Shapes.Paste
    ' Align pasted chart
    pptApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    pptApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
End Sub
0
votes

Try this instead. A few points:

You don't need a new variable for each chart/slide etc. One, reused as needed, is plenty.

Never use SELECT unless there's no way around it (in Excel or PPT). It makes the code more fragile and forces you to make the app visible (not really necessary most of the time). It also slows your code down by an order of magnitude since PPT has to redraw everything.

Sub MakeSlides()
Dim myData As Excel.Range
Dim sheet2 As Excel.Worksheet
Dim objPPT As Object
Set sheet2 = ActiveWorkbook.Sheets("Sheet2")
Set myData = sheet2.Range("A2:B43")
Set objPPT = CreateObject("Powerpoint.application")
myData.Copy
Dim pptApp As New PowerPoint.Application
pptApp.Visible = True
Dim pres As PowerPoint.Presentation
Set pres = pptApp.Presentations.Add
Dim oSlide As PowerPoint.Slide
Dim oChtShape as PowerPoint.Shape

Set oSlide = pres.Slides.Add(1, PowerPoint.PpSlideLayout.ppLayoutBlank)
Dim myChart As Excel.ChartObject
Set myChart = Sheet1.ChartObjects(1)
myChart.Copy
Set oChtShape = oSlide.Shapes.Paste(1)

' Align pasted chart
oChtShape.Align msoAlignCenters, True
oChtShape.Align msoAlignMiddles, True

' Not sure what this is supposed to do:
Set sheet2 = ActiveWorkbook.Sheets("Sheet2")
Set myData = sheet2.Range("A45:B69")
myData.Copy

' it's already visible; don't need this
'pptApp.Visible = True

' don't need a new object variable for each slide;
' reuse the existing variable instead
Set oSlide = pres.Slides.Add(1, PowerPoint.PpSlideLayout.ppLayoutBlank)
Set myChart = Sheet1.ChartObjects(2)
myChart.Copy

'secondslide.Shapes.Paste
Set oChtShape = oSlide.Shapes.Paste(1)

' Align pasted chart
oChtShape.Align msoAlignCenters, True
oChtShape.Align msoAlignMiddles, True

End Sub