Copy and paste Excel objects into Powerpoint using VBA

Walthobum · Dec 6th 2017

I have a workbook that creates a number of charts which I update every month. It would be very useful if I were able to copy and paste these charts into slides. However the way the charts work means I would need to see three charts grouped together on each slide, rather a single chart on a single slide. I'm never much good at explaining these things so hopefully the below might explain it better:

In the Excel Sheet I have

Chart1, Chart2 and Chart3, next to each other.
Chart4, Chart5 and Chart6, again next to each other.
Chart7, Chart8 and Chart9, also next to each other, and so on.

On the Excel sheet they make sense next to each other. One is actual performance against last year, one is a long term trend line, the final one if a plan against actual in year. In the Excel sheet I have set up the sheet so these charts sit next to each other and can be viewed easily. In the Powerpoint presentation I need to see them in the same way and the only way I can think to do that is to group them as below:

Chart1 + Chart2 + Chart3 grouped = Object1
Chart4 + Chart5 + Chart6 grouped = Object2
Chart7 + Chart8 + Chart9 grouped = Object3

How can I write some code that copies the objects, (I will group them in Excel if necessary), and pastes them into a single slide on Powerpoint for each object - i.e. - Slide1 contains Object1, Slide 2 contains Object2, Slide3 contains Object3 etc..

Many thanks.

Walthobum · Dec 6th 2017

I suppose the question is what sort of object do charts become when they are grouped? Are they shapes? Do I need to amend code to look for shapes and copy and paste them into Powerpoint instead of charts?

jolivanes · Dec 6th 2017

When you group the charts, give the group a name like "Group1"
You can refer to it as a shape with a name.
I don't know the first thing about copying into PP but I use this (found it googling)

Code

Sub ImportABunch()


Dim strTemp As String
Dim strPath As String
Dim strFileSpec As String
Dim osld As Slide
Dim oPic As Shape


' Edit these to suit:
strPath = "c:\Temp Pics\"
strFileSpec = "*.jpg"


strTemp = Dir(strPath & strFileSpec)


Do While strTemp <> ""
    Set osld = ActivePresentation.Slides.Add(ActivePresentation.Slides.Count + 1, ppLayoutBlank)
    Set oPic = osld.Shapes.AddPicture(FileName:=strPath & strTemp, _
    LinkToFile:=msoFalse, _
    SaveWithDocument:=msoTrue, _
    Left:=0, _
    Top:=0, _
    Width:=-1, _
    Height:=-1)
   With oPic
     If 9 * .Width > 16 * .Height Then    'Wide Screen Slide Size = 540 by 960
         .Width = ActivePresentation.PageSetup.SlideWidth
         .Top = 0.5 * (ActivePresentation.PageSetup.SlideHeight - .Height)
     Else
       .Height = ActivePresentation.PageSetup.SlideHeight
         .Left = 0.5 * (ActivePresentation.PageSetup.SlideWidth - .Width)
     End If
   End With




    ' Get the next file that meets the spec and go round again
    strTemp = Dir
Loop


End Sub

Display More

As you see though, they are imported as pictures from a temporary folder.
You might be able to change to fit your needs.
Good luck

Copy and paste Excel objects into Powerpoint using VBA

Participate now!

Share