VBA to export Excel worksheet to PowerPoint

  • Hi experts,


    I am using the following VBA to export my worksheet to PowerPoint file, however if I change the slicer in my Pivot Chart and export again the worksheet, the code is creating a new presentation. I would like to add the new selection using the same presentation, just adding as a new slide.


    Any suggestions, please.


    *******************************************************************************************************************************************************************************************************************************************************************************************************


    VBA Code:


    Sub ExceltoPowerPoint()


    Dim PowerPointApp As Object
    Dim myPresentation As Object
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim namecheck As Range


    Set PowerPointApp = GetPowerPointApp()
    Set myPresentation = PowerPointApp.Presentations.Add

    Call ExportResourcePlanSlide(myPresentation, ThisWorkbook.ActiveSheet.Range("a2:m40"))


    PowerPointApp.Visible = True
    PowerPointApp.Activate


    Application.CutCopyMode = False

    End Sub




    Function GetPowerPointApp() As Object


    On Error Resume Next
    Set PowerPointApp = GetObject(class:="PowerPoint.Application")
    Err.Clear
    If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject(class:="PowerPoint.Application")
    If Err.Number = 429 Then
    MsgBox "PowerPoint could not be found, aborting."
    Exit Function
    End If
    Set GetPowerPointApp = PowerPointApp
    On Error GoTo 0

    End Function



    Sub ExportResourcePlanSlide(ByVal myPresentation As Object, ByRef rng As Range)


    'Create new slide------------------------------------------------------------------------------------------------------


    Set myslide = myPresentation.Slides.Add(myPresentation.Slides.Count + 1, 12) '11 = ppLayoutTitleOnly


    'Copy range and paste to powerpoint------------------------------------------------------------------------------------


    rng.Copy
    myslide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile


    'Add Commentary Text Box-----------------------------------------------------------------------------------------------


    Set myTextBox = myslide.Shapes.AddTextbox(1, Left:=100, Top:=100, Width:=8.19 * 28.3465, Height:=350)


    With myTextBox
    .TextFrame.TextRange.Text = ""
    .TextFrame.TextRange.Font.Size = 10
    .Left = 24.9 * 28.3465
    .Top = 3.18 * 28.3465
    End With


    End Sub

  • royUK

    Closed the thread.