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