Excel VBA to Powerpoint - paste special chart object link

[FinUser]

Hi



I have this code below from @ John Peltier and it works great. However I want to change 2 things - can anyone help?



First: paste the charts in linked to the originals in excel so I can update them automatically. In other words what you get if you: Copy, Paste Special, Paste Link, “Microsoft Excel chart Object”.



Second: specify their position on the slide (ie, horizontal 2.04 and vertical 4.82)



Help hugely appreciated



Many thanks



style='font-family:"Arial",sans-serif'>Sub ChartsToPresentation()
' Set a VBE reference to Microsoft PowerPoint Object Library

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim PresentationFileName As Variant
Dim SlideCount As Long
Dim iCht As Integer

' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide

For iCht = 1 To ActiveSheet.ChartObjects.Count
' copy chart as a picture
ActiveSheet.ChartObjects(iCht).Chart.CopyPicture _
Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture

' Add a new slide and paste in the chart
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank)
PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
With PPSlide
' paste and select the chart picture
.Shapes.Paste.Select
' align the chart
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
End With

Next

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

End Sub

 

[Domenic]

When you say that you want to specify the horizontal and vertical position, I"m not sure whether you mean the left and top positions or the width and height. So I've assumed you want the left and top positions. If this is not what you meant, replace .Left and .Top with .Width and .Height.

' copy the chart
ActiveSheet.ChartObjects(iCht).Copy

' add a new slide
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank)

' paste and position linked chart
With PPSlide.Shapes.PasteSpecial(DataType:=ppPasteShape, Link:=msoTrue)(1)
    .Left = 2.04
    .Top = 4.82
End With

' go to the new slide (optional)
PPSlide.Select

Hope this helps!

 

[FinUser]

When you say that you want to specify the horizontal and vertical position, I"m not sure whether you mean the left and top positions or the width and height. So I've assumed you want the left and top positions. If this is not what you meant, replace .Left and .Top with .Width and .Height.

' copy the chart
ActiveSheet.ChartObjects(iCht).Copy

' add a new slide
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank)

' paste and position linked chart
With PPSlide.Shapes.PasteSpecial(DataType:=ppPasteShape, Link:=msoTrue)(1)
    .Left = 2.04
    .Top = 4.82
End With

' go to the new slide (optional)
PPSlide.Select

Hope this helps!

This is great, thanks so much. When I try and adjust the original code for it (as per below), I get issues with this line:

With PPSlide.Shapes.PasteSpecial(DataType:=ppPasteShape, Link:=msoTrue)(1)

Apologies for my ignorance, would you mind taking a look at it? thank you ever so much...

Sub ChartsToPresentation()
' Set a VBE reference to Microsoft PowerPoint Object Library


Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim PresentationFileName As Variant
Dim SlideCount As Long
Dim iCht As Integer


' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide

For iCht = 1 To ActiveSheet.ChartObjects.count

' copy the chart
ActiveSheet.ChartObjects(iCht).Copy

' add a new slide
SlideCount = PPPres.Slides.count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank)

' paste and position linked chart
With PPSlide.Shapes.PasteSpecial(DataType:=ppPasteShape, Link:=msoTrue)(1)
.Left = 2.04
.Top = 4.82
End With

Next

' go to the new slide (optional)
PPSlide.Select
End Sub

 

[Domenic]

Sorry, my mistake. Leave that line as is, but replace...

ActiveSheet.ChartObjects(iCht).Copy

with

ActiveSheet.ChartObjects(iCht).Chart.ChartArea.Copy

Hope this helps!