Select Embedded Documents

[Darrell Lankford]

Does anyone know a code that will select only the embedded documents
on a sheet? I’m trying to setup a code that will copy and paste the
embedded documents to another sheet, but I can’t figure out how to
keep the code from selecting all the other objects on the sheet. I
have Form Buttons, Option Buttons, & embedded PDF, Excel, or Word
documents. I only want the code to select the embedded documents.

I have tried…..
ActiveSheet.Shapes.SelectAll

I have tried…..
ActiveSheet.OLEObjects.Select

I have tried…..
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If shp.Type = msoEmbeddedOLEObject Then
ActiveSheet.Shapes.SelectAll
End If
Next

But, they all select the buttons with the embedded documents. I only
want to select the embedded documents. Any help would be greatly
appreciated.

 

[Joel]

You usually have to use the Type or Name ProgID of the object. here is what
I usually do

Sub Test()
For Each obj In ActiveSheet.OLEObjects

Next obj
End Sub

Then right click obj and slect add to watch. As you step through the code
using F8 look at the obj in the watch and look at the parameters "Name" and
"Type". These will help you select the correct object.

Sub Test()
For Each obj In ActiveSheet.OLEObjects
If InStr(obj.progID, "Word") Then

End If
Next obj
End Sub

 

[Peter T]

Sub AllEmbededOLEcopy()
Dim i As Long, cnt As Long
Dim ole As OLEObject

With ActiveSheet.OLEObjects
If .Count Then
ReDim arr(1 To .Count)
For i = 1 To .Count
If .Item(i).OLEType = xlOLEEmbed Then
cnt = cnt + 1
arr(cnt) = i
End If
Next
If cnt < i Then
ReDim Preserve arr(1 To cnt)
End If
End If
End With

If cnt Then
' the select stuff only to help reposition in same place
Range("A1").Select
ActiveSheet.OLEObjects(arr).Copy
Worksheets(2).Activate ' the destination sheet
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
End If

'Application.ScreenUpdating = True
End Sub

Regards,
Peter T



Does anyone know a code that will select only the embedded documents
on a sheet? I’m trying to setup a code that will copy and paste the
embedded documents to another sheet, but I can’t figure out how to
keep the code from selecting all the other objects on the sheet. I
have Form Buttons, Option Buttons, & embedded PDF, Excel, or Word
documents. I only want the code to select the embedded documents.

I have tried…..
ActiveSheet.Shapes.SelectAll

I have tried…..
ActiveSheet.OLEObjects.Select

I have tried…..
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If shp.Type = msoEmbeddedOLEObject Then
ActiveSheet.Shapes.SelectAll
End If
Next

But, they all select the buttons with the embedded documents. I only
want to select the embedded documents. Any help would be greatly
appreciated.

 

[Darrell Lankford]

I used Joel's code with a (MsgBox obj.progID) line and that told me
the type of each object.

Pete's code picked the objects that were just the PDF, Word, & Excel
embedded documents and not the buttons. Just what I need.

I thank both you guys for taking the time to help.
Darrell