Search code examples
vbahyperlinkpowerpoint

How to break links in OLEFormat object?


I have a PowerPoint 2007 slide with two embedded Excel objects.
I want to access the sheet1 tab and break all links.
I was trying to copy and paste by value. Although the code runs, it does nothing.

Dim oSl As Slide
Dim oSheet As Object
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
 
For Each oSl In ActivePresentation.Slides
    For Each oSh In oSl.Shapes

        ActiveWindow.View.GotoSlide oSl.SlideIndex
        If oSh.Type = msoEmbeddedOLEObject Then
            oSh.OLEFormat.Activate

            With oSh.OLEFormat.Object
               .Application.Workbooks(1).Worksheets(1).Cells.Copy
               .Application.Workbooks(1).Worksheets(1).Cells.PasteSpecial Paste:=xlPasteValues
            End With

            ActiveWindow.Selection.Unselect
            ActiveWindow.View.GotoSlide oSl.SlideIndex

        End If
    
    Next
    
Next
End Sub

Solution

  • Updated/edited ... skip the Application update line, try like so instead:

    If oSh.Type = msoEmbeddedOLEObject Then
        With oSh.OLEFormat.Object   ' added .Object here
            .Activate
            .Application.workbooks(1).worksheets(2).Cells.Copy
            .Application.workbooks(1).worksheets(2).Cells.PasteSpecial Paste:=xlPasteValues
        End With
    End if
    

    First, either define a constant lxPasteValues As Long = -4163 or replace xlPasteValues with -4163 in the code above.