1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83
| Sub Export()
Dim ppt As PowerPoint.Application
Dim PPTDOC As PowerPoint.Presentation
Dim pptApp As PowerPoint.Application
Dim ppSlide As PowerPoint.Slide
Dim j, NbreLigne As Long
Dim Tableau
Dim Chantier As String
Dim ObjShTable As PowerPoint.Shape
If Range("A4") = "" Then
MsgBox "Aucune sélection"
Exit Sub
End If
Application.ScreenUpdating = False
Tableau = Range("A4:A" & Range("A" & Rows.Count).End(xlUp).Row)
' On vérifie si on a un critère de la ListBox1
If Range(Sheets("Sheet1").ListBox1.LinkedCell) <> "" Then
Chantier = Range(Sheets("Sheet1").ListBox1.LinkedCell)
End If
' On se lie avec le PPT ouvert
Set pptApp = CreateObject("PowerPoint.application")
pptApp.Visible = True
'Set PPTDOC = PPTApp.Presentations.Add(msoTrue)
Set PPTDOC = pptApp.Presentations.Open(ThisWorkbook.Path & "\template.pptx")
Sheets("Trajectoire").Select
NbreLigne = Range("A" & Rows.Count).End(xlUp).Row ' Nombre de ligne du tableau page "Trajectoire"
' Ce filtre ne change pas alors on le place en dehors de la boucle
ActiveSheet.Range("$A$10:$P$" & NbreLigne).AutoFilter Field:=16, Criteria1:=Array("A", "B", "C", "D"), Operator:=xlFilterValues
' Ce filtre ne change pas alors on le place en dehors de la boucle
ActiveSheet.Range("$A$10:$P$" & NbreLigne).AutoFilter Field:=2, Criteria1:=Chantier
Columns("F").Hidden = True
For j = 1 To UBound(Tableau)
ActiveSheet.Range("$A$10:$P$" & NbreLigne).AutoFilter Field:=11, Criteria1:=Tableau(j, 1)
If Application.Subtotal(103, Range("A11:A" & NbreLigne)) > 0 Then ' Vérif si des lignes filtrées
'pptapp.ActivePresentation.Slides(J - 2).Select
'rajoute 1 slide
pptApp.ActivePresentation.Slides.Add pptApp.ActivePresentation.Slides.Count + 1, ppLayoutBlank
pptApp.ActiveWindow.View.GotoSlide pptApp.ActivePresentation.Slides.Count
'Copier la selection
Sheets("Trajectoire").Range("A10:K" & NbreLigne).SpecialCells(xlCellTypeVisible).Copy
With pptApp.ActivePresentation
'.Activate
pptApp.ActivePresentation.Slides(2).Select
pptApp.ActivePresentation.Slides(2).Shapes.PasteSpecial(ppPasteDefault, link:=True).Select
End With
With Selection
.Font.Size = 9
.Left = 120
.Top = 100
.Height = 500
.Width = 700
.LockAspectRatio = msoTrue
'.ScaleHeight ScaleFctr, True
'.ScaleWidth ScaleFctr, True
.Align msoAlignCenters, True
.Align msoAlignMiddles, True
End With
End If
Next j
ActiveSheet.AutoFilterMode = False
Columns("F").Hidden = False
Application.CutCopyMode = False
End Sub |
Partager