Search code examples
excelvbapowerpoint

Replace pasted range on a slide with an updated version


I have a sub() to replace a shape on a ppt slide. The existing shape is a range of cells pasted from Excel (PasteSpecial DataType:=0).

I am trying to replace the pasted range on the slide with an updated version of that same range. I believe my existing code is correct, except for the .TextEffect.Text line where I recieve

Type Mismatch error

Is this because it is not a single cell, but rather a range of cells?

Sub ReplaceTable()
    Dim PowerPointApp As PowerPoint.Application
    Dim myPresentation As PowerPoint.Presentation
    Dim i As Object
  
    Set PowerPointApp = GetObject(, "PowerPoint.Application")
    Set myPresentation = PowerPointApp.ActivePresentation
  
    Dim SlideIndex
    Dim SlideShape
    Dim ShapeContent

    For Each i In Worksheets("Sheet1").Range("H2:H" & Worksheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Row)
  
        SlideIndex = Worksheets("Sheet1").Range("H" & i.Row) 'moves to defined slide index
        SlideShape = "Content Placeholder 3" 'selects shape
        ShapeContent = Worksheets("Sheet2").Range("D9:V24") 'copies new tabular data to replace current table on slide
        myPresentation.Slides(SlideIndex).Shapes(SlideShape).TextEffect.Text = ShapeContent
    
    Next i
    
End Sub

Solution

  • Solved by deleting original shape and then replacing, as so:

    ShapeContent.Copy 
    myPresentation.Slides(SlideIndex).Shapes(SlideShape).Delete
    myPresentation.Slides(SlideIndex).Shapes.PasteSpecial DataType:=0