我在一个excel中有数百个图表。下面的代码创建了一个powerpoint,并根据一种模式将图表粘贴到powerpoint中。例如,有37个图表在许多维度上重复,例如Total_Portfolio有37个图表,CRA_Portfolio有37个图表,Fixed_Portfolio有37个图表.,....这种模式一直持续下去。
下面的代码在前5张幻灯片中每张幻灯片粘贴4个图表,然后在下一张幻灯片中粘贴3个图表,再在接下来的14张幻灯片中每张幻灯片粘贴1个图表。
所以,模式是4,4,4,4,4,3,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,并且重复直到报告所有维度。
如果我想调整代码,使每个维度有41个图表,幻灯片上的模式需要是4,2,3,3,3,2,4,2,4,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,然后重复,我应该如何调整下面的代码?
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
我有一个代码,假设37个图表的模式工作-需要调整41个图表。我看这里:(Creating a powerpoint with multiple charts on each slide from excel using vba),但这并不能真正解决每张幻灯片的图表数量问题。
1条答案
按热度按时间cig3rfwq1#
真的很难弄清楚,但下面似乎工作。