• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5486
  • Last Modified:

Keep Source Formatting pasting charts from Excel to PowerPoint using VBA

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
CThomp2005
Asked:
CThomp2005
  • 6
  • 5
1 Solution
 
dlmilleCommented:
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
 
CThomp2005Author Commented:
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
 
dlmilleCommented:
This might get you there.  I'm looking at it, now:

http://skp.mvps.org/pptxp001.htm
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
dlmilleCommented:
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
 
CThomp2005Author Commented:
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
 
CThomp2005Author Commented:
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
 
dlmilleCommented:
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
 
CThomp2005Author Commented:
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
 
CThomp2005Author Commented:
Thanks again - the solution was perfect (referencing the commandbars object to invoke the shortcut option).  Greatly appreciated!
0
 
dlmilleCommented:
Which loop worked for you:

Loop with DoEvents?

DoEvents by Itself?

The loop until no error?

Dave
0
 
dlmilleCommented:
on the last, putting a DoEvents in is probably warranted, as it could bog your machine down, lol.

Dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now