Changing code to adjust number of charts on each powerpoint slide VBA

0 votes

In an excel file, I have hundreds of charts. According to a pattern, the following code makes a powerpoint and pastes the charts into it. There are 37 charts, for instance, that appear repeatedly throughout multiple dimensions. For instance, the Total Portfolio includes 37 charts, followed by the CRA Portfolio with 37 charts, the Fixed Portfolio with 37 charts, and so on.

The code below inserts four charts on each slide for the first five slides, followed by three charts on the following slide, one chart on each slide for the following fourteen slides.

So, the pattern is 4,4,4,4,4,3,1,1,1,1,1,1,1,1,1,1,1,1,1,1 and that repeats until all dimensions are reported.  How would I modify the following code such that each dimension contains 41 charts and the pattern throughout the slides is 4, 2, 3, 2, 4, 1, 1, 1, 1, 1, 1, 1, 1, and then repeat?

Option Explicit

Sub CopyChartsToPowerPoint()

'// excel variables/objects
Dim wb As Workbook
Dim source_sheet As Worksheet
Dim chart_obj As ChartObject
Dim i As Long, last_row As Long, tracker As Long

'// powerpoint variables/objects
Dim pp_app As PowerPoint.Application
Dim pp_presentation As Presentation
Dim pp_slide As Slide
Dim pp_shape As Object
Dim pp_slider_tracker As Long

Set wb = ThisWorkbook
Set source_sheet = wb.Worksheets("portfolio_charts")
Set pp_app = New PowerPoint.Application
Set pp_presentation = pp_app.Presentations.Add

last_row = source_sheet.Cells(Rows.Count, "A").End(xlUp).Row
pp_slider_tracker = 1

Set pp_slide = pp_presentation.Slides.Add(pp_slider_tracker, ppLayoutBlank)

For i = 1 To last_row

If i Mod 37 = 5 Or i Mod 37 = 9 Or i Mod 37 = 13 Or i Mod 37 = 17 _
Or i Mod 37 = 21 Or (i Mod 37 > 23 And i Mod 37 < 37) Or i Mod 37 = 0 Or (i Mod 37 = 1 And pp_slider_tracker > 1) Then
pp_slider_tracker = pp_slider_tracker + 1
Set pp_slide = pp_presentation.Slides.Add(pp_slider_tracker, ppLayoutBlank)
End If

Set chart_obj = source_sheet.ChartObjects(source_sheet.Cells(i, "A").Value)
chart_obj.Chart.ChartArea.Copy

'Set pp_shape = pp_slide.Shapes.PasteSpecial(ppPasteEnhancedMetafile)
Set pp_shape = pp_slide.Shapes.Paste

Select Case i Mod 37

Case 1, 5, 9, 13, 17
pp_shape.Left = 66
pp_shape.Top = 86

Case 2, 6, 10, 14, 18
pp_shape.Left = 510
pp_shape.Top = 86

Case 3, 7, 11, 15, 19
pp_shape.Left = 66
pp_shape.Top = 296

Case 4, 8, 12, 16, 20
pp_shape.Left = 510
pp_shape.Top = 296

Case 21
pp_shape.Left = 66
pp_shape.Top = 86

Case 22
pp_shape.Left = 510
pp_shape.Top = 86

Case 23
pp_shape.Left = 66
pp_shape.Top = 296

Case 24 To 37, 0
pp_shape.Left = 192
pp_shape.Top = 90
pp_shape.width = 576
pp_shape.height = 360

End Select

Application.Wait (Now + TimeValue("00:00:01"))

Next i

End Sub

I have a code that works assuming a pattern of 37 charts - need to adjust for 41 charts. I looked here: (Creating a powerpoint with multiple charts on each slide from excel using vba) but that doesn't really address the number of charts per slide.

Jan 23 in Others by Kithuzzz
• 27,740 points
35 views

1 answer to this question.

0 votes

Try this:

Option Explicit

Sub CopyChartsToPowerPoint()
    
    '// excel variables/objects
    Dim wb As Workbook
    Dim source_sheet As Worksheet
    Dim chart_obj As ChartObject
    Dim i As Long, last_row As Long, tracker As Long
    
    '// powerpoint variables/objects
    Dim pp_app As PowerPoint.Application
    Dim pp_presentation As Presentation
    Dim pp_slide As Slide
    Dim pp_shape As Object
    Dim pp_slider_tracker As Long
    
    Set wb = ThisWorkbook
    Set source_sheet = wb.Worksheets("portfolio_charts")
    
    Set pp_app = New PowerPoint.Application
    Set pp_presentation = pp_app.Presentations.Add
    
    last_row = source_sheet.Cells(Rows.Count, "A").End(xlUp).Row
    
    pp_slider_tracker = 1
    
    Set pp_slide = pp_presentation.Slides.Add(pp_slider_tracker, ppLayoutBlank)
    
    For i = 1 To last_row
        'Stop
        'Debug.Assert i < 36
        
        If (i Mod 41 = 1 And pp_slider_tracker > 1) Or i Mod 41 = 5 Or i Mod 41 = 7 Or i Mod 41 = 10 Or i Mod 41 = 13 Or i Mod 41 = 16 Or i Mod 41 = 18 Or i Mod 41 = 22 Or i Mod 41 = 24 Or _
        (i Mod 41 > 27 Or i Mod 41 = 0) Then
            
            pp_slider_tracker = pp_slider_tracker + 1
            Set pp_slide = pp_presentation.Slides.Add(pp_slider_tracker, ppLayoutBlank)
            
        End If
        
        Set chart_obj = source_sheet.ChartObjects(source_sheet.Cells(i, "A").Value)
        chart_obj.Chart.ChartArea.Copy
                     
        'Set pp_shape = pp_slide.Shapes.PasteSpecial(ppPasteEnhancedMetafile)
        Set pp_shape = pp_slide.Shapes.Paste
        
        Select Case i Mod 41
        
            Case 1, 5, 7, 10, 13, 16, 18, 22, 24
                pp_shape.Left = 66
                pp_shape.Top = 86

            Case 2, 6, 8, 11, 14, 17, 19, 23, 25
                pp_shape.Left = 510
                pp_shape.Top = 86

            Case 3, 9, 12, 15, 20, 26
                pp_shape.Left = 66
                pp_shape.Top = 306

            Case 4, 21, 27
                pp_shape.Left = 510
                pp_shape.Top = 306
                
        End Select
        
        Application.Wait (Now + TimeValue("00:00:01"))
    Next i

End Sub
answered Jan 23 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
1 answer

How to increment the Range of a For Each loop - Excel VBA

Your formula seems to sum 1 single ...READ MORE

answered Jan 7 in Others by narikkadan
• 51,240 points
58 views
0 votes
1 answer

VBA code to select only a table. I am getting a Run-time error '1004'; Method 'Range' of object'_Global' failed

No copy/paste, just direct assignment use.Value Sub Final_Report() ...READ MORE

answered Jan 13 in Others by narikkadan
• 51,240 points
64 views
0 votes
1 answer
0 votes
1 answer

How to do Installation of Ruby on Rails on Linux (Ubuntu)?

Hi Pratibha, installing ROR on ubuntu is ...READ MORE

answered Mar 1, 2019 in Others by Anvi
• 14,170 points
1,055 views
0 votes
1 answer

How to modify Powerpoint Chart ChartData by Excel VBA

Example: Code: Set pptApp = GetObject(, "PowerPoint.Application") Set pptPres = ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 51,240 points
396 views
0 votes
1 answer

Using excel I need to open PPT and create ".gif" image of a ."pdf" and save it

It appears happier if you get a ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 51,240 points
52 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
522 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,382 views
0 votes
1 answer

Excel VBA to change background image of shape by clicking on shape

You need to keep track of what ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 51,240 points
446 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP