Proper way to replace and delete an embedded object?

[gdesreu]

OK, what I am doing is running a parameter query. Based on the users selection it pulls in and embeds a pdf file based on a link put into cell A1. The problem is when the user selects another file. My current process deletes all of the oleobjects on the page (because I couldnt figure out how to just delete this pdf embedded object as it is named differently each time it is pulled in)
My current process worked fine up until I needed to add some controls (radio buttons) now when it loads the next file it deletes the radio buttons. Is there a way to name this embedded pdf file so that I can then delete only that object? or is there a fundamental better way to handle this?

below is the section in question

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    Set KeyCells = Range("A1")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
      
        Dim wsh As Worksheet
For Each wsh In ActiveWorkbook.Worksheets
    wsh.OLEObjects.Delete
Next wsh
        ActiveCell.Offset(0, 0).Range("j1").Select
    ActiveSheet.OLEObjects.Add(Filename:=Range("a1").Value, Link:= _
        False, DisplayAsIcon:=False).Select

 

[Jerry Sullivan]

Renaming the object when you embed it could work, but it might be simpler to test each OLEObject's progID Property and delete any that match a certain type. On my system, PDF's are embedded with the progID: "AcroExch.Document.7"

If your system is similar, you could use something like this...

Dim ole As OLEObject
Dim wsh As Worksheet
For Each wsh In ActiveWorkbook.Worksheets
    For Each ole In wsh.OLEObjects
        Debug.Print ole.progID & ": " & ole.Name
        If Left(ole.progID, 8) = "AcroExch" Then ole.Delete
    Next ole
Next wsh

Test this on a copy of your workbook first!

The Debug.Print statement will display each objects progID in the Immediate Window of the VB Editor. If your PDFs' progIDs don't have the "AcroExch" prefix you can adapt the code to work with their progID.

 

[gdesreu]

Renaming the object when you embed it could work, but it might be simpler to test each OLEObject's progID Property and delete any that match a certain type. On my system, PDF's are embedded with the progID: "AcroExch.Document.7"

If your system is similar, you could use something like this...

Dim ole As OLEObject
Dim wsh As Worksheet
For Each wsh In ActiveWorkbook.Worksheets
    For Each ole In wsh.OLEObjects
        Debug.Print ole.progID & ": " & ole.Name
        If Left(ole.progID, 8) = "AcroExch" Then ole.Delete
    Next ole
Next wsh

Test this on a copy of your workbook first!

The Debug.Print statement will display each objects progID in the Immediate Window of the VB Editor. If your PDFs' progIDs don't have the "AcroExch" prefix you can adapt the code to work with their progID.

This worked absolutely perfect for me first try! It was exactly what I needed. I cant thank you enough. I was starting to lose sleep over this and Im up against deadlines.
Thank you!

 

[Jerry Sullivan]

I'm happy to hear that. Thanks for providing feedback.