?
Solved

Keep Source Formatting pasting charts from Excel to PowerPoint using VBA

Posted on 2011-10-16
11
Medium Priority
?
5,050 Views
Last Modified: 2012-06-27
I'm working on a project where I'm batch-producing presentations in PowerPoint - embedding charts created in Excel 2010 using VBA.  The charts are set up in Excel and the data populated there via VBA.  Once done, a PowerPoint template is opened (from within the program) and, one by one, the charts are copied and pasted into the presentation.  (The program runs from Excel and setups up a PowerPoint object to manipulate the presentation.)

Easy enough, right?

PowerPoint 2010 annoyingly wants to apply the template's formatting to the charts.  I can manually right-click and select "Keep Source Formatting and Embed Chart" to get it to keep the source color scheme (which I want), but I can't seem to find any way to replicate this using VBA.  I've gone through all the .PasteSpecial options, but none seem to duplicate the shortcut menu option mentioned above.  The closest I can come is the ppPasteOLEObject, but the result is pretty grainy, and requires the user to double-click on the chart to open and edit it (which they want to be able to do).  Using the "Keep Source Formatting..." option above allows you to edit the chart directly, as if a native object.

Does anyone know if this command is replicable using VBA, or if not?  I've discovered that the key-combination to do this is Alt+H+V+K - and I was thinking of trying to use SendKeys, but it doesn't seem to work applying it against the PowerPoint application reference in my code (i.e., PPApp.SendKeys - gives and error).

Thanks much for any help!
0
Comment
Question by:CThomp2005
  • 6
  • 5
11 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 36977676
Have you tried pasting a bitmap, or better yet (in my opinion) an enhanced metafile as opposed to embedding an OLE Object?  One would think if you're batch producing, you might consider pasting images, as opposed to embedding what could be an impressively sized OLE Object.

Dave
0
 
LVL 3

Author Comment

by:CThomp2005
ID: 36977715
The end user wants to be able to click/double-click the embedded charts in the presentations and edit if they need to.  Worst-case scenario would be pasting some image format and sending along the source Excel charts.

The only hang-up for me is finding out if the "Keep source formatting..." option is available through VBA, otherwise, I'll have to consider images...
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36977834
This might get you there.  I'm looking at it, now:

http://skp.mvps.org/pptxp001.htm
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 36977876
John Wilson in this thread (Powerpoint 2010 specific) suggests trying to execute the commandbar option to paste preserving formatting.  As a result, I've successfully tested this, but can't verify as I'm all with generic formatting so can't visibly tell the difference, tho the commandbar option IS RUN, otherwise nothing would have been pasted.

Its good to note (a new one on me, though have only done primitive stuff thru trial/error with Powerpoint) that you can execute commandbar options - as opposed to using SendKeys!).

The focus command is on line 28

Here's some test code to try out:

Assumes the active workbook/worksheet has a chart object that will be pasted into a new powerpoint presentation.

 
'Source adapted from:  http://www.computing.net/answers/office/paste-excel-graphs-to-powerpoint/7040.html
Sub copyPasteChartToPPT()
Dim myCht As Object
Dim pptApp As PowerPoint.Application
Dim pptPres As PowerPoint.Presentation
Dim pptSlide As PowerPoint.Slide
Dim pptShape As PowerPoint.Shape


    Set pptApp = New PowerPoint.Application
    pptApp.Visible = True
    
    Set pptPres = pptApp.Presentations.Add(msoTrue)
    
    With pptPres.Slides
        Set pptSlide = .Add(.Count + 1, ppLayoutTitleOnly) ' add a slide
    End With
 
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.ChartArea.Copy
    

    With pptSlide
         '.Shapes(1).TextFrame.TextRange.Text = "Slide Title" 'edit to put a generic title on each slide or
         ' take this line out if you dont want a generic slide title to appear on each slide
         '.Shapes.PasteSpecial DataType:=ppPasteOLEObject, DisplayAsIcon:=msoFalse
        
        pptApp.CommandBars.ExecuteMso ("PasteSourceFormatting")
        
        'With .Shapes(.Shapes.Count) ' sizes the graph on the slide
        '    .Left = 120
        '    .Top = 125.125
        '    .Width = 480
        '    .Height = 289.625
        'End With

    End With
 
    Application.CutCopyMode = False
    Set pptSlide = Nothing
    
End Sub

Open in new window


See attached workbook.

Cheers,

Dave
chartToPPT-r1.xlsm
0
 
LVL 3

Author Comment

by:CThomp2005
ID: 36979181
Thanks for the research dlmille, that does look like it should work.  I won't be able to verify until I get into work this morning to plug it in and test it.

I've done 99% of my programming in Office 2003 apps, but have recently read through some 2010 VBA books - and now that you've shown me the code, I do recall seeing the usage of .ExecuteMSO for running commands.  Just didn't hit me before now.

Thanks much!  I'll reply back as soon as I can plug it in and run it!
0
 
LVL 3

Author Comment

by:CThomp2005
ID: 36982439
Thanks again, dlmille...  I plugged that one line in (line 38), and it does indeed paste in the way I need it.  There is a small side-effect - a timing issue - it seems, as it appears to take a second or two for the object to paste in and be recognized by PowerPoint as a legit object.

After pasting in, I immediately attempt to adjust the .Top and .Left and it throws an error, basically, that it can't find the object (or "ShapeRange").  If I step through manually, no problem.  I put in a loop right after the paste (.ExecuteMso) to allow time for the chart to appear:
      for waittime = 1 to 8000:  DoEvents: next waittime

...with this loop, it seems to run fine.  I know this is a pretty crude way of doing this - do you have a better/preferred method of "waiting" until something is done (or for a specific amount of time)?

Thanks again.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36982473
Try using DoEvents without the loop and see if that's enough.

You could also have a loop that sets a pointer to the shape range and when you finally don't get an error, the work is done - caution, re: infinite loops!

e.g.,

on error resume next
do
    err.clear
    set x = shapeThatwasjustPasted

loop while err.number <> 0
0
 
LVL 3

Author Comment

by:CThomp2005
ID: 36982770
Thanks again.  I will try the DoEvents by itself first.

(shaking my head) I tried the do-loop method, but missed something that I see in yours.  I had:

on error resume next
do
   [code to try to set the .Top of the shape]
loop until err = 0

Obviously, I forgot the err.clear - and I was locked in a loop.

Thanks again for all the help.  You've been a lifesaver...
0
 
LVL 3

Author Closing Comment

by:CThomp2005
ID: 36982775
Thanks again - the solution was perfect (referencing the commandbars object to invoke the shortcut option).  Greatly appreciated!
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36982815
Which loop worked for you:

Loop with DoEvents?

DoEvents by Itself?

The loop until no error?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36982820
on the last, putting a DoEvents in is probably warranted, as it could bog your machine down, lol.

Dave
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question