Solved

Pasting an Excel chart into Powerpoint using VBA

Posted on 2001-09-14
2
1,461 Views
Last Modified: 2008-03-10
I know how to paste an Excel chart into Powerpoint using VBA, but is there a way to paste it as a picture?

When I tried to record a macro in Powerpoint using paste special > picture, VBA code is not generated.

Any ideas?

Phil
0
Comment
Question by:phil_hearn
  • 2
2 Comments
 
LVL 44

Accepted Solution

by:
bruintje earned 100 total points
Comment Utility
Hi Phil, only a workaround here because that's teh nature of the beast

from an answer somewhere else

The macro below simulates a PasteSpecial as Enchanced Meta file routine
The sub routine picks up the chart from excel, creates an instance of word,
pastes the chart as an enchanced meta file into it, then copies the metafile
back to the clipboard, finally pasting the meta file into the current slide
in powerpoint

No error handling has been provided in the example so please incorprorate
it. Hope it helps.

' ==== Beginning of Code ====
Sub OurOwnPasteSpecial()
   Dim wrdDoc As Object
   Dim wrdApp As Object
   Dim xlApp As Object
   Dim xlWrkBook As Object
   Dim lCurrSlide As Long

    Set xlApp = CreateObject("Excel.Application")
         Set xlWrkBook = xlApp.Workbooks.Open("C:\BOOK1.XLS")

    xlWrkBook.Worksheets(1).ChartObjects(1).Copy

    ' As Brian mentioned in an earlier posting, there  is no PasteSpecial
    ' method available in PowerPoint. Hence we have to circumvent
    ' that by either using the PasteSpecial of Excel or Word and then
    ' copying the Picture back into PowerPoint

   ' Get the slide number to paste the meta file later.
    lCurrSlide = ActiveWindow.Selection.SlideRange.SlideNumber
   ' Create a Word Instance.
    Set wrdApp = CreateObject("Word.Application")
    Set wrdDoc = wrdApp.documents.Add()

   ' Pastes the information on the Clipboard into the Word document as
   ' an Enhanced Metafile.
   With wrdApp.ActiveWindow.Selection
        .PasteSpecial DataType:=wdPasteEnhancedMetafile
        .Copy  ' Copy the MetaFile back to the clipboard
   End With

   ' Paste the metafile onto the PowerPoint slide.
   ActivePresentation.Slides(lCurrSlide).Shapes.Paste

   ' Close the document without saving changes.
   wrdDoc.Close (wdDoNotSaveChanges)
   wrdApp.Quit

   ' Close the open workbook without saving changes
    xlWrkBook.Close (False)
    xlApp.Quit

    Set xlApp = Nothing
    Set xlWrkBook = Nothing
    Set wrdApp = Nothing
    Set wrdDoc = Nothing
End Sub
' ==== End of Code ====
--
Regards
Shyam Pillai

you see this is a hell of work around using Word and the clipboard to get something of your liking

HTH:O)Bruintje
0
 
LVL 44

Expert Comment

by:bruintje
Comment Utility
Manually, you can do it and it works very well. Programmatically, the command is not available to PPT in VBA, which is in the other programs in Office. The development team on PPT VBA seems to just not have had
enough time to implement this in prior versions since they do indicate that there is no reason that it could not be done since it is inherent in the core VBA code model.

from Brian Reilly Powerpoint MVP.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

How many times recently have you prepared a presentation or emailed a document to a client and you have found that they have older versions of MS Office and they can not open the file you have prepared.  Although most visitors to this site are exper…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now