Update to VBA that loops through Charts and Copies to PowerPoint to include text boxes

kfryfry

New Member
Joined
Feb 22, 2016
Messages
8
Hey,

I found this code from Export All Excel Charts To Power Point ~ My Engineering World (thanks) and I'd like to update it so that the code pulls any text boxes on the same worksheet and copies them to the same powerpoint slide as where the chart comes from. I want it to continue to run through so that from each worksheet all the charts and the textboxes end up on one slide (one slide per per worksheet).
And for bonus points how do I get it to center all the copied items horizontally on the powerpoint slide?

I hope this is clear enough, I've been playing about with the code and can make either all charts and text boxes from one page move across, or all the charts or all the text boxes, but I am struggling to combine this.

PHP:
'Declaring the necessary Power Point variables (are used in both subs).
Dim pptApp As PowerPoint.Application
Dim pptPres As PowerPoint.Presentation
Dim pptSlide As PowerPoint.Slide
Dim pptSlideCount As Integer

Sub ChartsToPowerPoint()

    'Exports all the chart sheets to a new power point presentation.
    'It also adds a text box with the chart title.
    
    'By Christos Samaras
    'http://www.myengineeringworld.net
    
    Dim ws As Worksheet
    Dim intChNum As Integer
    Dim objCh As Object
    
    'Count the embedded charts.
    For Each ws In ActiveWorkbook.Worksheets
        intChNum = intChNum + ws.ChartObjects.Count
    Next ws
    
    'Check if there are chart (embedded or not) in the active workbook.
    If intChNum + ActiveWorkbook.Charts.Count < 1 Then
        MsgBox "Sorry, there are no charts to export!", vbCritical, "Ops"
        Exit Sub
    End If
    
    'Open PowerPoint and create a new presentation.
    Set pptApp = New PowerPoint.Application
    Set pptPres = pptApp.Presentations.Add
    
    'Loop through all the embedded charts in all worksheets.
    For Each ws In ActiveWorkbook.Worksheets
        For Each objCh In ws.ChartObjects
            Call pptFormat(objCh.Chart)
        Next objCh
    Next ws
    
    'Loop through all the chart sheets.
    For Each objCh In ActiveWorkbook.Charts
        Call pptFormat(objCh)
    Next objCh
    
    'Show the power point.
    pptApp.Visible = True

    'Cleanup the objects.
    Set pptSlide = Nothing
    Set pptPres = Nothing
    Set pptApp = Nothing
    
    'Infrom the user that the macro finished.
    MsgBox "The charts were copied successfully to the new presentation!", vbInformation, "Done"
    
End Sub

Private Sub pptFormat(xlCh As Chart)
    
    'Formats the charts/pictures and the chart titles/textboxes.
    
    'By Christos Samaras
    'http://www.myengineeringworld.net
    
    Dim chTitle As String
    Dim j As Integer
    
    On Error Resume Next
   'Get the chart title and copy the chart area.
    chTitle = xlCh.ChartTitle.Text
    xlCh.ChartArea.Copy

    'Count the slides and add a new one after the last slide.
    pptSlideCount = pptPres.Slides.Count
    Set pptSlide = pptPres.Slides.Add(pptSlideCount + 1, ppLayoutBlank)
    
    'Paste the chart and create a new textbox.
    pptSlide.Shapes.PasteSpecial ppPasteJPG
    If chTitle <> "" Then
        pptSlide.Shapes.AddTextbox msoTextOrientationHorizontal, 12.5, 20, 694.75, 55.25
    End If
                    
    'Format the picture and the textbox.
    For j = 1 To pptSlide.Shapes.Count
        With pptSlide.Shapes(j)
            'Picture position.
            If .Type = msoPicture Then
                .Top = 87.84976
                .Left = 33.98417
                .Height = 422.7964
                .Width = 646.5262
            End If
            'Text box position and formamt.
            If .Type = msoTextBox Then
                With .TextFrame.TextRange
                    .ParagraphFormat.Alignment = ppAlignCenter
                    .Text = chTitle
                    .Font.Name = "Tahoma (Headings)"
                    .Font.Size = 28
                    .Font.Bold = msoTrue
                End With
            End If
        End With
    Next j

End Sub
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Bump - I'm pretty stuck still :confused:

Essentially I want to be able to pull off text boxes (two) and one chart from each page in a worksheet and have the shapes from each worksheet appear on the same powerpoint slide, one per worksheet.

Any help will have great thanks!
 
Upvote 0
Hello kfryfry.

Did you come up with a solution for this problem?
If so, could you please post it as I can't figure it out myself either.

BR, Brejne
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top