Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Copying Charts from Excel 2007 to PPT 2007 - How to bring in Entire WorkbookCode?

Avatar of Bryce Bassett
Bryce BassettFlag for United States of America asked on
Visual Basic ClassicMicrosoft ExcelMicrosoft PowerPoint
13 Comments1 Solution1447 ViewsLast Modified:
Hi, Experts:

I've got a VBA macro that cuts a chart from Excel 2007 and pastes it into an active PowerPoint 2007 slide. It's working fine, except I want my code to replicate the behavior you get when you manually Paste, then choose Paste Options and select "Excel Chart (entire workbook)."  This is VITAL because I need to break the connection to the source workbook and embed the data into PowerPoint for future editing.  Anybody know how to do this programmatically?

(Most of what I've read online says bringing in the "entire workbook" is the default paste method.  Not true for me, I get links every time.  Or they warn you not to use this option because you'll end up with huge files or including confidential data. I get around that by first having my macro copy the chart and it's source data to a new workbook, then paste over to PPT.)

Here are the relevant snippets of my code.  

THANKS for the help!
'CODE RUNS IN EXCEL 2007

' COPY USER SELECTED DATA RANGE TO A NEW WORKBOOK
    oRangeSelected.Copy
    Workbooks.Add
    Range("A1").Select
    ActiveSheet.Paste

' CREATE A CHART FROM THE NEWLY PASTED DATA, THEN CUT CHART    
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range(CurrentRegion)
    ActiveChart.ChartType = xl3DPie
    ActiveSheet.ChartObjects("Chart 1").Activate
    Selection.Cut
  
'ACTIVATE THE CURRENT PPT APPLICATION AND SLIDE
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide

    Set PPApp = GetObject(, "Powerpoint.Application")
    Set PPPres = PPApp.ActivePresentation
    PPApp.ActiveWindow.ViewType = ppViewSlide
    Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
    
'PASTE THE CHART WHICH WAS CUT FROM EXCEL
    PPSlide.Shapes.Paste.Select
'HERE'S WHERE I NEED HELP. HOW CAN I PASTE IT SO IT USES THE 
'EXCEL CHART(ENTIRE WORKBOOK) PASTE OPTION??
ASKER CERTIFIED SOLUTION
Avatar of StellanRosengren
StellanRosengrenFlag of Sweden image

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 13 Comments.
See Answers