CODEWITHSUNDEEP
vbahyperlinkpowerpoint
mooseman
mooseman

Reputation: 2015

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

Upvotes: 0

Views: 1644

Answers (1)

Steve Rindsberg
Steve Rindsberg

Reputation: 3528

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.

Upvotes: 1

Related Questions