+ Reply to Thread
Results 1 to 6 of 6

Need Help in editing the Macro to export Charts to PPt

  1. #1
    Registered User
    Join Date
    12-16-2015
    Location
    kENTUCKY
    MS-Off Ver
    2010
    Posts
    5

    Need Help in editing the Macro to export Charts to PPt

    Hi guys I have this code which helps in exporting ppt from excel to powerpoint.

    What Macro Does:
    Exports Charts from excel work book to PPT

    What I want it to Do:
    can you please help me in editing the macro so it copies charts from worksheet.so what I mean it has to be edited in suc a way that it takes only charts/ Charts from active sheet and not whole file.


    CODE:

    Final Macro

    Sub PushChartsToPPT()
    'Set reference to 'Microsoft PowerPoint 12.0 Object Library'
    'in the VBE via Tools > References...
    '
    Dim ppt As PowerPoint.Application
    Dim pptPres As PowerPoint.Presentation
    Dim pptSld As PowerPoint.Slide
    Dim pptCL As PowerPoint.CustomLayout
    Dim pptShp As PowerPoint.Shape

    Dim cht As Chart
    Dim ws As Worksheet
    Dim i As Long

    'Get the PowerPoint Application object:
    Set ppt = CreateObject("PowerPoint.Application")
    ppt.Visible = msoTrue
    Set pptPres = ppt.Presentations.Add

    'Get a Custom Layout:
    For Each pptCL In pptPres.SlideMaster.CustomLayouts
    If pptCL.Name = "Title and Content" Then Exit For
    Next pptCL

    'Copy ALL charts in Chart Sheets:
    For Each cht In ActiveWorkbook.Charts
    Set pptSld = pptPres.Slides.AddSlide(pptPres.Slides.Count + 1, pptCL)
    pptSld.Select

    For Each pptShp In pptSld.Shapes.Placeholders
    If pptShp.PlaceholderFormat.Type = ppPlaceholderObject Then Exit For
    Next pptShp
    If pptShp Is Nothing Then Stop

    cht.ChartArea.Copy
    ppt.Activate
    pptShp.Select
    ppt.Windows(1).View.Paste
    Next cht

    'Copy ALL charts embedded in EACH WorkSheet:
    For Each ws In ActiveWorkbook.Worksheets
    For i = 1 To ws.ChartObjects.Count
    Set pptSld = pptPres.Slides.AddSlide(pptPres.Slides.Count + 1, pptCL)
    pptSld.Select

    For Each pptShp In pptSld.Shapes.Placeholders
    If pptShp.PlaceholderFormat.Type = ppPlaceholderObject Then Exit For
    Next pptShp

    Set cht = ws.ChartObjects(i).Chart
    cht.ChartArea.Copy
    ppt.Activate
    pptShp.Select
    ppt.Windows(1).View.Paste
    Next i
    Next ws
    End Sub

    Also can we add this line of code to the above macro-

    activeSlide.Shapes(1).TextFrame.TextRange.Text = cht.Chart.ChartTitle.Text

    so basically what it does is that it Sets the title of the slide the same as the title of the chart
    Last edited by dirtmankx65; 12-17-2015 at 02:16 AM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Need Help in editing the Macro to export Charts to PPt

    Try something like this (not tested).

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    12-16-2015
    Location
    kENTUCKY
    MS-Off Ver
    2010
    Posts
    5

    Re: Need Help in editing the Macro to export Charts to PPt

    Quote Originally Posted by AlphaFrog View Post
    Try something like this (not tested).

    Please Login or Register  to view this content.



    Thanks Alpha frog it worked like charm only thing I had to edit the below code line and remove chart to make macro work.
    pptSld.Shapes(1).TextFrame.TextRange.Text = cht.Chart.ChartTitle.Text
    Attached Images Attached Images
    Last edited by dirtmankx65; 12-17-2015 at 02:13 AM.

  4. #4
    Registered User
    Join Date
    12-16-2015
    Location
    kENTUCKY
    MS-Off Ver
    2010
    Posts
    5

    Re: Need Help in editing the Macro to export Charts to PPt

    Final Macro ( Tested)
    Thanks to Alpha frog.

    Sub PushChartsToPPT()
    'Set reference to 'Microsoft PowerPoint 12.0 Object Library'
    'in the VBE via Tools > References...
    '
    Dim ppt As PowerPoint.Application
    Dim pptPres As PowerPoint.Presentation
    Dim pptSld As PowerPoint.Slide
    Dim pptCL As PowerPoint.CustomLayout
    Dim pptShp As PowerPoint.Shape

    Dim cht As Chart
    Dim ws As Worksheet
    Dim i As Long

    'Get the PowerPoint Application object:
    Set ppt = CreateObject("PowerPoint.Application")
    ppt.Visible = msoTrue
    Set pptPres = ppt.Presentations.Add

    'Get a Custom Layout:
    For Each pptCL In pptPres.SlideMaster.CustomLayouts
    If pptCL.Name = "Title and Content" Then Exit For
    Next pptCL

    'Copy ALL charts embedded in Active WorkSheet:
    Set ws = ActiveSheet
    For i = 1 To ws.ChartObjects.Count
    Set pptSld = pptPres.Slides.AddSlide(pptPres.Slides.Count + 1, pptCL)
    pptSld.Select

    For Each pptShp In pptSld.Shapes.Placeholders
    If pptShp.PlaceholderFormat.Type = ppPlaceholderObject Then Exit For
    Next pptShp

    Set cht = ws.ChartObjects(i).Chart
    pptSld.Shapes(1).TextFrame.TextRange.Text = cht.ChartTitle.Text
    cht.ChartArea.Copy
    ppt.Activate
    pptShp.Select
    ppt.Windows(1).View.Paste
    Next i
    End Sub

  5. #5
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Need Help in editing the Macro to export Charts to PPt

    Please use code tags so your code looks like AlphaFrog's.

    When you post you can either copy all you code, then click the # on the toolbar and put your cursor between the ## that come up and select paste, or you could paste it first and then highlight all of it and then select the # on the toolbar.

    You would be really appreciated around here if you would choose to edit your posts here in this thread by choosing edit then highlighting all of your code click the # and then choose to save the changes.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Need Help in editing the Macro to export Charts to PPt

    You're welcome. Thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 32
    Last Post: 07-17-2018, 03:08 AM
  2. Macro to Export Charts as PNG
    By JFERG in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-14-2014, 08:10 AM
  3. change order of graphs when using macro to export all excel charts to powerpoint
    By silencer27 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-15-2014, 06:48 AM
  4. Editing Pie Charts
    By dsciola in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 07-03-2013, 01:50 PM
  5. editing charts data
    By longhorn23 in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 08-18-2010, 11:18 AM
  6. Excel 2007 : Editing multiple charts on a worksheet
    By InGroup in forum Excel General
    Replies: 1
    Last Post: 10-21-2009, 02:12 PM
  7. EDITING CHARTS
    By FLKULCHAR in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-17-2005, 01:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1