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
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.