+ Reply to Thread
Results 1 to 8 of 8

Extract Group of Excel chart to PPT Slide and put a header for that group

  1. #1
    Registered User
    Join Date
    03-23-2022
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    4

    Question Extract Group of Excel chart to PPT Slide and put a header for that group

    Hello,

    I am stuck with a complex situation.

    I have 78 Excel Pivot Charts in an Excel.
    I need to extract them in a PowerPoint Slide as Image but every slide should have 6 charts only and that group of 6 charts needs to have a Header on top of it.

    I have referred to SigmaCoding videos (on YouTube) and his GitHub code, but unable to crack the logic for macro in this case.

    The charts are of same size and are aligned. So, I am trying to group the 6 charts and then extract that group in a PPT slide and have a header for that Slide.

    The headers are static so if we can copy the group of charts in a existing PPT template that would work too.

    Can anyone please help me with this? Stuck since 1 week with this use case.

    Thanks in advance for the help and consideration.

  2. #2
    Valued Forum Contributor
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    2016 & 365 Sub
    Posts
    620

    Re: Extract Group of Excel chart to PPT Slide and put a header for that group

    Are there 6 charts in each excel tab with a header or do you have all of these charts in one sheet?

    If you can it would be worth uploading a dummy workbook with the structure of your data nd maybe a ppt slide with the output.

  3. #3
    Registered User
    Join Date
    03-23-2022
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    4

    Re: Extract Group of Excel chart to PPT Slide and put a header for that group

    Thanks for your reply.

    All the charts are in one excel, is it possible to do that way? Or we need to split that in different sheets?

    Headers are static, we can even add that in Macro.

    unable to add the attachment (may be due to the new user restrictions)

  4. #4
    Valued Forum Contributor
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    2016 & 365 Sub
    Posts
    620

    Re: Extract Group of Excel chart to PPT Slide and put a header for that group

    I was thinking about writing the code - when using chart objects we tend to loop through the objects that exist on the worksheet. With this in mind how would we know which chart belongs in which group?

    It could be the chart name?

    I imagine your data to be all in one worksheet with a row as a header with 6 charts underneath - then another header row with 6 more charts underneath etc...:
    If the above is the case then we can't group by those headers but if the charts were named differently per group or each group was in it's own worksheet then this would be a simpler task.

  5. #5
    Registered User
    Join Date
    03-23-2022
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    4

    Re: Extract Group of Excel chart to PPT Slide and put a header for that group

    Thanks for your time.

    We could use the chart name or we can mention the range in which the charts are there and loop through that to see the objects?

    If you think having different sheets will be a better solution we can go with that too.

    Also is there a way we can have the 6 charts grouped together and then copy that group in PPT slide and mention the header above that?

  6. #6
    Valued Forum Contributor
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    2016 & 365 Sub
    Posts
    620

    Re: Extract Group of Excel chart to PPT Slide and put a header for that group

    I am sure someone here can do it you need to decide how the data is going to be.
    I would be more tempted to go for six charts on each tab with the tab name being the name of the header for the slide.

    The code would then be a loop through the sheets:
    1 Get to sheet via loop
    2 Add slide
    3 Add slide title/ header
    4 Copy charts to ppt and align them
    Next sheet (back to number 1)

  7. #7
    Registered User
    Join Date
    03-23-2022
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    4

    Re: Extract Group of Excel chart to PPT Slide and put a header for that group

    Thanks for this, can you please help with the macro? I am new to macros.

    Below is the script I am trying to edit:

    -------------
    Attribute VB_Name = "Module1"
    Sub ManipulateShapeInPowerPoint()

    'Declare PowerPoint Variables
    Dim PPTApp As PowerPoint.Application
    Dim PPTPres As PowerPoint.Presentation
    Dim PPTSlide As PowerPoint.Slide
    Dim PPTShape As PowerPoint.Shape

    'Dim Excel Variables
    Dim Chrt As ChartObject

    'Create a new PowerPoint Application & make it visible.
    Set PPTApp = New PowerPoint.Application
    PPTApp.Visible = True
    PPTApp.Activate

    'Create a new presentation in the PowerPoint Application
    Set PPTPres = PPTApp.Presentations.Add

    'Create a new slide in the PowerPoint Presentation
    Set PPTSlide = PPTPres.Slides.Add(1, ppLayoutTitleOnly)

    'Create a Chart Object variable where specify the sheet the chart is on and the index number of that chart.
    Set Chrt = Worksheets("Object").ChartObjects(1)

    'Copy the Chart Object variable we specified above.
    Chrt.Copy

    'Paste the Chart Object on the Slide that we created above.
    PPTSlide.Shapes.Paste

    'Set a reference to the shape we want to manipulate
    Set PPTShape = PPTSlide.Shapes(2)
    'Set PPTShape = PPTSlide.Shapes(PPTSlide.Shapes.Count)

    'Set the height, width, top & left of the shape
    With PPTShape
    .Left = 100
    .Top = 100
    .Height = 300
    .Width = 300
    End With

    End Sub


    Sub AligningShapesInPowerPointAlign_MethodOne()

    'Declare PowerPoint Variables
    Dim PPTApp As PowerPoint.Application
    Dim PPTPres As PowerPoint.Presentation
    Dim PPTSlide As PowerPoint.Slide
    Dim PPTShape As PowerPoint.Shape

    'Dim Excel Variables
    Dim Chrt As ChartObject

    'Create a new PowerPoint Application & make it visible.
    Set PPTApp = New PowerPoint.Application
    PPTApp.Visible = True
    PPTApp.Activate

    'Create a new presentation in the PowerPoint Application
    Set PPTPres = PPTApp.Presentations.Add

    'Create a new slide in the PowerPoint Presentation
    Set PPTSlide = PPTPres.Slides.Add(1, ppLayoutTitleOnly)

    'Create a Chart Object variable where specify the sheet the chart is on and the index number of that chart.
    Set Chrt = Worksheets("Object").ChartObjects(1)

    'Copy the Chart Object variable we specified above.
    Chrt.Copy

    'Paste the Chart Object on the Slide that we created above.
    PPTSlide.Shapes.Paste

    'Set a reference to the shape we want to manipulate
    Set PPTShape = PPTSlide.Shapes(PPTSlide.Shapes.Count)

    'Set the height & width of the shape
    With PPTShape
    .Height = 300
    .Width = 300
    End With

    'Get the Slide Width & Slide Height
    SldHeight = PPTPres.PageSetup.SlideHeight
    SldWidth = PPTPres.PageSetup.SlideWidth

    'Calculate the Slide Center and Slide Middle
    SldMiddle = (SldHeight / 2)
    SldCenter = (SldWidth / 2)

    'Calculate Shape Center and Shape Middle
    ShpMiddle = (PPTShape.Height / 2)
    ShpCenter = (PPTShape.Width / 2)

    'Center the shape and align it to the middle
    With PPTShape
    .Left = SldCenter - ShpCenter
    .Top = SldMiddle - ShpMiddle
    End With


    End Sub



    Sub AligningShapesInPowerPointAlign_MethodTwo()

    'Declare PowerPoint Variables
    Dim PPTApp As PowerPoint.Application
    Dim PPTPres As PowerPoint.Presentation
    Dim PPTSlide As PowerPoint.Slide
    Dim PPTShape As PowerPoint.Shape
    Dim PPTShapeRng As PowerPoint.ShapeRange
    Dim ShpCount As Integer

    'Dim Excel Variables
    Dim Chrt As ChartObject

    'Create a new PowerPoint Application & make it visible.
    Set PPTApp = New PowerPoint.Application
    PPTApp.Visible = True
    PPTApp.Activate

    'Create a new presentation in the PowerPoint Application
    Set PPTPres = PPTApp.Presentations.Add

    'Create a new slide in the PowerPoint Presentation
    Set PPTSlide = PPTPres.Slides.Add(1, ppLayoutTitleOnly)

    'Create a Chart Object variable where specify the sheet the chart is on and the index number of that chart.
    Set Chrt = Worksheets("Object").ChartObjects(1)

    'Copy the Chart Object variable we specified above.
    Chrt.Copy

    'Paste the Chart Object on the Slide that we created above.
    PPTSlide.Shapes.Paste

    'Count Shapes on Slide
    ShpCount = PPTSlide.Shapes.Count

    'Create a reference to a shape range that will contain multiple shapes.
    Set PPTShapeRng = PPTSlide.Shapes.Range(Array(ShpCount))

    'Set the height & width of the shape.
    With PPTShapeRng
    .Height = 300
    .Width = 300
    End With

    'Align Shape to the middle & center of the SLIDE
    PPTShapeRng.Align msoAlignCenters, True
    PPTShapeRng.Align msoAlignMiddles, True

    End Sub


    Sub AligningShapesInPowerPointAlign_MethodThree()

    'Declare PowerPoint Variables
    Dim PPTApp As PowerPoint.Application
    Dim PPTPres As PowerPoint.Presentation
    Dim PPTSlide As PowerPoint.Slide

    'Dim Excel Variables
    Dim Chrt As ChartObject

    'Create a new PowerPoint Application & make it visible.
    Set PPTApp = New PowerPoint.Application
    PPTApp.Visible = True
    PPTApp.Activate

    'Create a new presentation in the PowerPoint Application
    Set PPTPres = PPTApp.Presentations.Add

    'Create a new slide in the PowerPoint Presentation
    Set PPTSlide = PPTPres.Slides.Add(1, ppLayoutTitleOnly)

    'Create a Chart Object variable where specify the sheet the chart is on and the index number of that chart.
    Set Chrt = Worksheets("Object").ChartObjects(1)

    'Copy the Chart Object variable we specified above.
    Chrt.Copy

    'Paste the Chart Object on the Slide that we created above & select that shape
    PPTSlide.Shapes.Paste.Select

    'Set dimensions of the shape
    With PPTApp.ActiveWindow.Selection.ShapeRange

    .Height = 300
    .Width = 300
    .Align msoAlignMiddles, True
    .Align msoAlignCenters, True

    End With


    End Sub

    ------------

  8. #8
    Valued Forum Contributor
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    2016 & 365 Sub
    Posts
    620

    Re: Extract Group of Excel chart to PPT Slide and put a header for that group

    You are goint to need to update a dummy workbook, see the yellow bar at the top about adding a worksheet as attachment.
    Get the data as you wish it to be, you probably only need 3 or 4 sheets for the example.

+ 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: 0
    Last Post: 07-11-2019, 05:36 AM
  2. Replies: 0
    Last Post: 03-07-2018, 11:07 AM
  3. Replies: 14
    Last Post: 01-12-2017, 01:41 PM
  4. How to make excel Group, Sub Group converted to tree structure in HTML
    By TSR Tech in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-29-2014, 01:37 PM
  5. Replies: 1
    Last Post: 02-10-2014, 10:26 PM
  6. Need to copy header row in group to all rows below header; stop if blank and repeate
    By jmcaleer10 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2013, 11:09 AM
  7. selecting group of charts to copy to a powerpoint slide
    By amid in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-10-2009, 03:31 AM

Tags for this Thread

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