Clear excel cache / ole object issue

gdesreu

Active Member
Joined
Jul 30, 2012
Messages
318
I'm having an odd issue with excel 2007 and Im hoping someone knows what is going on and how to fix it. The code below basically copies in a pdf and embeds it. The code works fine. The user then loads another file and the code below reruns deleting and replacing the embedded pdf. (again works fine) the problem is each time you do this if you do anything that causes the screen to refresh it cycles through all previous pdfs. So if you loaded 100 files cycles through the previous 99 before loading the 100th. Doesnt do this in 2010 but it does keep storing data in the clipboards of 2007 and 2010 as well. If you exit excel and re-open it clears the issue and it starts over. How do I program this correctly to avoid the issue? Is there a way to clear the memory?
Also Application.CutCopyMode = False doesnt work either.
Thanks


Code:
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
Dim ole As OLEObject
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
        ActiveCell.Offset(0, 0).Range("j1").Select
    ActiveSheet.OLEObjects.Add(Filename:=Range("a1").Value, Link:= _
        False, DisplayAsIcon:=False).Select
       
      Sheets("reporting").Select
      Sheets("reporting").Range("a1").Select
      ActiveCell.Offset(0, 0).Range("b13").Select
  ActiveSheet.OLEObjects.Add(Filename:=Sheets("report tool").Range("A1").Value, Link:= _
        False, DisplayAsIcon:=False).Select
       
      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.SendToBack
    Next ole
Next wsh
    End If
    
End Sub
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top