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

    Application.CutCopyMode = False

    End Sub

    Function GetPowerPointApp() As Object

    On Error Resume Next
    Set PowerPointApp = GetObject(class:="PowerPoint.Application")
    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------------------------------------------------------------------------------------

    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

  • Moses 76

    Welcome to Ozgrid. Please read the Forum Rules then post your question again in a new post. Posting questions in other members threads is not allowed

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!