Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Embedded Documents
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Embedded Documents
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 "Darrell Lankford" wrote: Does anyone know a code that will select only the embedded documents on a sheet? Im trying to setup a code that will copy and paste the embedded documents to another sheet, but I cant 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Embedded Documents
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 "Darrell Lankford" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Embedded Documents
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting a worksheet with embedded documents in one column | Excel Discussion (Misc queries) | |||
embedded documents in excel | Excel Discussion (Misc queries) | |||
embedded Excel sheets in Word documents | Excel Programming | |||
Coping Embedded documents to local drive... | Excel Discussion (Misc queries) | |||
Printing multiple embedded Word Documents | Excel Programming |