Solved

Pasting an Excel chart into Powerpoint using VBA

Posted on 2001-09-14
2
1,481 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
ID: 6483187
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
ID: 6483528
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
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  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 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