Delete an embedded OLEobject - pdf attachment

[punkS]

I am trying to create an application where user can upload a file say .pdf and delete the same.

"how to change the attachment name in name bar so that I can refer it in my code."


At present the counter changes every time I attach a file for eg. I insert a pdf xyz it gives it name "object 5"


I delete it and reinsert but now counter has moved to "object 6".

Hence I am not able to refer it with its name in VBA code.


Any help would be highly appreciated


Thanks in Advance!!!!

 

[punkS]

I recorded following macro but it uses the object name "Object 89". How to change name while inserting this pdf:-


ActiveSheet.Shapes("Object 89").Select

ActiveWorkbook.Names.Add Name:="design1", RefersToR1C1:="=""Object 89"""

 

[shrivallabha]

You can do try like following if the intention is to delete pdfs:

[pre]

Public Sub DelPdf()
Dim oo As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
On Error Resume Next
For Each oo In ws.OLEObjects
'Please check the progID shown for pdf documents on your machine
If oo.progID Like "AcroExch*" Then
oo.Delete
End If
Next oo
End Sub

[/pre]

 

[punkS]

@shrivallabha thanks for reply !!!


i want to delete a particular file say the user attaches 5 files in the excel.


they should have name design1 from upload tab 1

and design2,... from upload tab 2


so that he could delete individual files.


how can i upload my file here so that the problem is clearly understood!!

 

[punkS]

I got the solution for the problem:-

vFile = Application.GetOpenFilename("All Files,*.*", Title:="Find file to insert", MultiSelect:=False)

If LCase(vFile) = "false" Then Exit Sub

Set oleObj = ActiveSheet.OLEObjects.Add(FileName:=vFile, Link:=False, DisplayAsIcon:=True, IconFileName:= _

"packager.exe", _

IconIndex:=0, IconLabel:="DesignReport1")

oleObj.Name = "some name"


But still one thing can I limit the size of attachment say 1mb.

 

[punkS]

I want to use your code for deleting all inserted pdf in my worksheet but some how its deleting only one object only......


Public Sub DelPdf()

Dim oo As OLEObject

Dim ws As Worksheet

Set ws = ActiveSheet

On Error Resume Next

For Each oo In ws.OLEObjects

'Please check the progID shown for pdf documents on your machine

If oo.progID Like "AcroExch*" Then

oo.Delete

End If

Next oo

End Sub


what could be the cause????