OLEObjects collections for embedded files

[mamuscia]

I am having the worse time trying to manipulate the OLEObjects
collection. I am able to add objects to my Excel 2002 workbook, but I
have difficulty when trying to reference the objects I added by NAME.
Here is the code I use to add the imbedded file objects. How would I
then reference these objects by the NAME that I gave them instead of
the INDEX number. Does the INDEX number change as objects are deleted
from a Worksheet? Thanks.

filesel = Application.GetOpenFilename _
(FileFilter:="All files (*.*), *.*", _
Title:="Select Files to Attach", MultiSelect:=True)
If Not IsArray(filesel) Then Exit Sub

ws.Activate
OLEnum = ActiveSheet.OLEObjects.Count
For i = 1 To UBound(filesel)
thisFullName = filesel(i)
thisFile = Dir(thisFullName)
o = o + 1
ActiveSheet.OLEObjects.Add(Filename:=thisFullName, _
Link:=False, DisplayAsIcon:=True, _
IconFileName:="packager.exe", _
IconIndex:=0, IconLabel:=thisFile).Name = thisFile
OLEnum = OLEnum + 1
ActiveSheet.OLEObjects(OLEnum).Name = thisFile
With ActiveSheet.OLEObjects(OLEnum)
.top = rg.Cells(c).top
.Left = rg.Cells(c).Left
.Width = rg.Cells(c).Width
.Height = rg.Cells(c).Height
End With
c = c + 1
With Me.boxAttachments
.AddItem OLEnum
.List(boxAttachments.ListCount - 1, 1) = thisFile
End With
Next i

 

[John]

ActiveSheet.OLEObjects("OBJETC_NAME").Name
--
Regards,
Luc.

"Festina Lente"

I have tried doing that but it does not work. Since I assign names as
the objects are added, I figured this would work too. I have tried
referencing them as follows and nothing works except using the Index
number which I found out does change everytime one is deleted or added.

Dim sName as String
sName = "MyfileName.txt"
ActiveSheet.OLEObjects(sName).Delete ' This does not work


Any other suggestions?

 

[Peter T]

Have a go with this -

For i = 1 To ActiveSheet.OLEObjects.Count
If ActiveSheet.OLEObjects(i).name = sName Then
ActiveSheet.OLEObjects(i).Delete
'Exit For
End If
Next

Regards
Peter T

 

[John]

Have a go with this -

For i = 1 To ActiveSheet.OLEObjects.Count
If ActiveSheet.OLEObjects(i).name = sName Then
ActiveSheet.OLEObjects(i).Delete
'Exit For
End If
Next

Regards
Peter T

This works. I would still like to be able to reference these objects
via name, but this works for my purposes.

Thanks.

 

[Peter T]

This works. I would still like to be able to reference these objects
via name, but this works for my purposes.

Thanks.

Dim shp As Shape, oOLE As OLEObject

Set shp = ActiveSheet.Shapes(sName)

' if you want to delete
'shp.Delete

'or work with the OLE
Set oOLE = shp.DrawingObject

Regards,
Peter T

 

[Peter T]

PS

You may find it easier to get straight at OLEObjects & drawingobjects in
general if you don't use punctuation like "." in the name unless as it seems
you need to. Punctuation is much less restricted with the shapes collection.

Peter T