asked on

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

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??

Open in new window

puppydogbuddy

see this link: http://peltiertech.com/Excel/XL_PPT.html
John Peltier suggests copying chart as a picture (see code snippet below)

' Copy chart as a picture
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, _
Format:=xlPicture

' Paste chart
PPSlide.Shapes.Paste.Select

' Align pasted chart
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

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

Read more: http://peltiertech.com/Excel/XL_PPT.html#ixzz0hh4l83uv

Bryce Bassett

ASKER

Thanks, puppydogbuddy.

I've already checked out Peltier's site. Good stuff but not helpful in answering my question. As I mentioned, for my application I need to bring the DATA into PowerPoint along with the chart so it's editable after the source spreadsheet becomes unavailable, so copying as a picture does me no good.

Any other suggestions, anybody?

Bryce Bassett

ASKER

If anyone knew of a KEYBOARD SHORTCUT to access the "Paste Options" dialog that is available after you manually paste, I guess I could use send keys to get at it that way.

puppydogbuddy

Can you access the paste special command and get to the paste options from there?

Bryce Bassett

ASKER

Nope, already looked at that too. Pasting as "Excel Chart (entire workbook)" is not one of the choices under Paste Special. Thanks.

Bryce Bassett

ASKER

I'm starting to think an easier way about this would be to keep the chart creation in Powerpoint, which spawns an internal Excel sheet already embedded within the powerpoint file. Then I just need to automate the process of grabbing real data from an existing worksheet and copying it back into the chart's sheet, replacing the dummy data. Then when I close it, the data is already in powerpoint. I can add a macro button on an Excel add-in to open the other sheet, ask then to select a range, and copy it over to the PPT sheet.

What do you think? Any other advice?

puppydogbuddy

I have heard that there are problems manipulating excel once it gets in ppt, so I wouldn't change methods yet.

Have you tried accessing excel paste from a fully qualified name of the chart object rather than "activeChart". If that does not help, try wrapping the Application object around the chart object and see what happens.

StellanRosengren

Hi versatilebb,

I created this solution which works fine on my Office 2003.
I think the trick is to use pastespecial and specify no link. Please have a look and come back if you need more assistance.

Kind regards,
Stellan

Sub InsertChartAsEmbeddedObject()

    ActiveSheet.ChartObjects.Add(50, 50, 100, 100).Select
    ActiveChart.SetSourceData Source:=ActiveCell.CurrentRegion
    ActiveChart.ChartType = xl3DPie
    ActiveSheet.ChartObjects("Chart 1").Activate
    Selection.Copy
   
'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")
    'Add a new presentation
    Set PPPres = PPApp.Presentations.Add(msoTrue)
    PPApp.ActiveWindow.ViewType = ppViewSlide
    'Add the first slide
    Set PPSlide = PPPres.Slides.Add(1, ppLayoutBlank)
     
'PASTE THE CHART WHICH WAS COPIED FROM EXCEL
    'Use PasteSpecial
    PPSlide.Shapes.PasteSpecial DataType:=ppPasteOLEObject, displayasicon:=msoFalse, link:=msoFalse

End Sub

Open in new window

Bryce Bassett

ASKER

Thanks for taking a look at this Stellan. Your code above does work in PowerPoint 2007, but it creates the chart as an OLE object. PowerPoint still considers it a drawing, and you can't access any of PPT 2007's new chart formatting of layout features. In other words, it's a suppored legacy format.

As I understand it, Microsoft updated their charting engine (Microsoft Chart) when they went from 2003 to 2007. PowerPoint now launches Excel when you go to create a chart. Later when you want to edit the chart, you just right-click, choose edit data, and the embedded Excel sheets opens up. I was hoping for this kind of a more "native 2007" solution.

Sorry but I'm leaving for the weekend and I can't spend any more time on this today. If you think of anything over the weekend, please chime in, but I won't be able to look again until Monday.

Thanks, Bryce

StellanRosengren

Hi Bryce,
That was interesting. In Powerpoint 2003 it behaves as you ask for. Apparently there is some difference which I am still not aware of when upgrading to the 2007 version. I will look into this later today.
I have installed Office 2007 and will try to figure out how to adapt the code. Please be patient.

Kind regards,
Stellan

ASKER CERTIFIED SOLUTION

StellanRosengren

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bryce Bassett

ASKER

Stellan:

I appreciate your persistence in looking at this for me. You've come to the same conclusion I did in my comment of 03/09/10 10:19 AM, ID: 27609501, which is, rather than try to paste in the chart, I need to directly copy my data into the Excel worksheet that is embedded within PowerPoint. I've experimented and can successfully address that sheet by name since it's always consistently named. The link you provided should help me get the rest of the way there. Thanks again!

Bryce

StellanRosengren

Bryce,
I am glad that I could help. Thank you for the points.

Stellan