Search and Replace Embedded Chart in PowerPoint

[yurble_vn]

Thanks Aussiebear,

I modified my code as following.
- I have to add in one fake step "Select Slide" when move to next slide. This fixed the issue on "VBA stop editing Embedded chart from 2nd slide onward".
- This code works fine except one thing: I have to open an Excel sheet in advance to make this code works. It seems the Open New Excel Instance did not work.

I did test this code without the that fake step of "select slide", it does not work.

Sub Chart___Testing()
    Dim oPres       As Presentation
    Dim oSlide      As Slide
    Dim oShape      As Shape
    Dim myChart     As PowerPoint.Chart
    Dim Wb          As Object
    Dim excelApp    As Object
    Dim answer      As Integer
    Dim ExecFlag    As Boolean
    
    'Reconfirm before Execute
    answer = MsgBox("This macro will update linked data in all charts, and embedded Excels." _
             & vbCrLf & "And update dyanmic tables that named 'Data'/'Table1' in 'Data'/'Sheet1' Sheets." _
             & vbCrLf & "DOES NOT WORK ON GROUPED CHARTS" & vbCrLf & "BACKUP FILE BEFORE RUNNING." & vbCrLf & "Continue?" _
             , vbQuestion + vbYesNo + vbDefaultButton2, "WARNING")
    
    If answer = vbNo Then
        GoTo ErrorHandler
    End If
    
    ExecFlag = False
    
    ' Get the active presentation
    Set oPres = ActivePresentation
    
    ' Attempt to get an existing Excel instance, or create a new one if none exists
    On Error Resume Next
    Set excelApp = GetObject(, "Excel.Application")
    On Error GoTo 0
    If excelApp Is Nothing Then
        Set excelApp = CreateObject("Excel.Application")
    End If
    ' Keep Excel hidden unless explicitly needed for user interaction
    excelApp.Visible = False
    excelApp.DisplayAlerts = False
    ' To prevent prompts
    
    ' Loop through each slide
    For Each oSlide In oPres.Slides
        oSlide.Select
        ' Loop through each shape on the slide
        
        For Each oShape In oSlide.Shapes
            
            'Update PowerPointChart
            If oShape.HasChart Then
                Set myChart = oShape.Chart
                myChart.ChartData.Activate
                ExecFlag = True
                                
            Else
                If oShape.Type = msoEmbeddedOLEObject Then
                    Set Wb = oShape.OLEFormat.Object
                    On Error Resume Next
                    'Check if the OLE object is an Excel Workbook
                    If TypeName(Wb) = "Workbook" Then
                        oShape.OLEFormat.Activate
                        ExecFlag = True
                    End If
                End If
            End If
            If ExecFlag Then
                ' Set the active workbook
                Set Wb = excelApp.ActiveWorkbook
                On Error Resume Next
                'Update Link
                Wb.UpdateLink Name:=Wb.LinkSources
                On Error GoTo 0
                'Change Cell BZ1 value
                Wb.Worksheets("Data").Range("BZ1").Value = "AABBDED"
                'Search and Replace
                Wb.Worksheets("Data").Range("A1:M50").Replace "Apple", "Banana"
                'Update Filter table
                On Error Resume Next
                Wb.Worksheets("Data").ListObjects("Data").Range.AutoFilter Field:=1, Criteria1:="<>0"
                On Error Resume Next
                Wb.Worksheets("Sheet1").ListObjects("Data").Range.AutoFilter Field:=1, Criteria1:="<>0"
                On Error Resume Next
                Wb.Worksheets("Data").ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="<>0"
                On Error Resume Next
                Wb.Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="<>0"
                'Refresh Screen and Close
                Wb.RefreshAll
                Wb.Close SaveChanges:=False   ' Specify not to save changes
                
                'Put A Updated Textbox
                Dim Tb As Shape
                Set Tb = oSlide.Shapes.AddTextbox(msoTextOrientationHorizontal, Left:=oShape.Left, Top:=oShape.Top, Width:=100, Height:=25)
                Tb.Name = "Delete_" & oShape.Name
                Tb.Fill.ForeColor.RGB = RGB(255, 255, 0)
                Tb.TextFrame.TextRange.Text = "Updated Data"
                Tb.TextFrame.TextRange.Font.Size = 8
                Tb.Select
                'Turn off Execution Flag to check again next Shape
                ExecFlag = False
            End If
            
        Next oShape
    Next oSlide
CleanUp:
        ' Ensure Excel is closed if we created a new instance
        excelApp.DisplayAlerts = True
        If excelApp Is Nothing Then
            ' Do nothing as we didn't create it
        Else
            ' Check if any workbooks are still open
            If excelApp.Workbooks.Count = 0 Then
               excelApp.Quit
            End If
        End If
        
        Set excelApp = Nothing
        ' Release the object variable
ErrorHandler:     MsgBox "Finish!"
   
End Sub