How to integrate charts from Excel into powerPoint

User avatar
chamdan
3StarLounger
Posts: 372
Joined: 17 Dec 2013, 00:07

How to integrate charts from Excel into powerPoint

Post by chamdan »

Hi Hans,

Is there a way to integrate the charts 1 and 2 from an Excel workbook into PowerPoint into the corresponding location using VBA. How can determine the position of location 2 as shown in the PowerPoint I have enclosed herewith? I am pretty sure that someone must have done that but wondering how to do it. The VBA behind it must be complex, which beyond of my ability. Any help would be appreciated.

Regards,

Chuck
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to integrate charts from Excel into powerPoint

Post by HansV »

Hi Chuck,

Do you want to run the code from the Excel workbook containing the charts, or from the PowerPoint presentation containing the slide?
Best wishes,
Hans

User avatar
chamdan
3StarLounger
Posts: 372
Joined: 17 Dec 2013, 00:07

Re: How to integrate charts from Excel into powerPoint

Post by chamdan »

Thanks for your prompt reply Hans. To reply to your question I want to run it from the Excel workbook containing the Chart. Using Office 2007.

Cheers!

Chuck

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to integrate charts from Excel into powerPoint

Post by HansV »

We'll start in PowerPoint.
Activate the slide where you want to paste the charts.
Select the first placeholder where you want a chart.
Press Alt+F11 to activate the Visual Basic Editor.
Press Ctrl+G to activate the Immediate window.
Type

? ActiveWindow.Selection.ShapeRange.Name

and press Enter. You should see something like Content Placeholder 4. Make a note of this name (you can also copy/paste it later on).
Do the same for the other placeholder.

Now switch to Excel.
In the following macro I will assume that the charts are ChartObjects(1) and ChartObjects(2) on the active sheet. If you know the name of the chart, you can also use ChartObjects("Chart 1") or similar.
I will also assume that the PowerPoint presentation is open and that the relevant slide is the active slide.

Code: Select all

Sub Chart2PPT()
    Dim objPPT As Object
    Dim objSlide As Object
    Dim objShape1 As Object
    Dim objShape2 As Object
    Dim cht As ChartObject

    Set objPPT = GetObject(Class:="PowerPoint.Application")
    Set objSlide = objPPT.ActiveWindow.View.Slide

    ' First Chart
    Set cht = ActiveSheet.ChartObjects(1)
    cht.Chart.ChartArea.Copy
    Set objShape1 = objSlide.Shapes("Content Placeholder 4") ' substitute correct name
    objSlide.Shapes.Paste
    Set objShape2 = objSlide.Shapes(objSlide.Shapes.Count)
    objShape2.Top = objShape1.Top
    objShape2.Left = objShape1.Left
    objShape2.Width = objShape1.Width
    objShape2.Height = objShape1.Height
    objShape1.Delete

    ' Second Chart
    Set cht = ActiveSheet.ChartObjects(2)
    cht.Chart.ChartArea.Copy
    Set objPPT = GetObject(Class:="PowerPoint.Application")
    Set objSlide = objPPT.ActiveWindow.View.Slide
    Set objShape1 = objSlide.Shapes("Content Placeholder 5") ' substitute correct name
    objSlide.Shapes.Paste
    Set objShape2 = objSlide.Shapes(objSlide.Shapes.Count)
    objShape2.Top = objShape1.Top
    objShape2.Left = objShape1.Left
    objShape2.Width = objShape1.Width
    objShape2.Height = objShape1.Height
    objShape1.Delete
End Sub
Best wishes,
Hans

User avatar
chamdan
3StarLounger
Posts: 372
Joined: 17 Dec 2013, 00:07

Re: How to integrate charts from Excel into powerPoint

Post by chamdan »

:clapping: :cheers: :fanfare: :thankyou:
From the first shot Hans, wonderful.

If I want to have more than two placeholders to fit more charts example 4 let us, I can use the same logic right? But I could not find in PowerPoint a layout that provide multiple placeholders where I could integrate multiple charts. Is this feasible?

Chuck

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to integrate charts from Excel into powerPoint

Post by HansV »

There is no built-in layout with three or more placeholders, but you can either create a custom layout in Slide Master View and apply the custom layout to your slide, or place some extra text boxes on the slide with the size and position you want and refer to them in the code.
Best wishes,
Hans

User avatar
chamdan
3StarLounger
Posts: 372
Joined: 17 Dec 2013, 00:07

Re: How to integrate charts from Excel into powerPoint

Post by chamdan »

:thankyou: :cheers:

Have a wonderful weekend!

Chuck