PDA

View Full Version : Automation error - Element not found



Caddyman
06-01-2008, 11:32 PM
Hi

i use the following bit of code to delete Hyperlinks when I cut and paste data from the internet to excel.

Sub RemoveHyperlinks()
'Remove all hyperlinks from the active sheet
ActiveSheet.Hyperlinks.Delete
End Sub

It works very well most of the time, BUT when I get a large amount of data, last example was just over 2000 lines with a single hyprelink in each row, I get the following error.



Run-time error '-2147319765 (8002802b)':

Automation error
Element not found.

:dunno :dunno
When i debug it highlights ActiveSheet.Hyperlinks.Delete


Any help you can provide is much appreciated.

Thanx
Grant

Jan Karel Pieterse
06-02-2008, 12:06 AM
Maybe you need to do them one-by-one (not tested):

Dim lCount as long
'Step in reverse so Excel doesn't get confused
For lCount=Hyperlinks.Count To 0 Step -1
Hyperlinks(lCount).Delete
Next

xld
06-02-2008, 12:45 AM
Small correc tion to Jan Karel's code



Dim lCount As Long
'Step in reverse so Excel doesn't get confused
For lCount = ActiveSheet.Hyperlinks.Count To 1 Step -1
ActiveSheet.Hyperlinks(lCount).Delete
Next

Jan Karel Pieterse
06-02-2008, 02:48 AM
Thanks xld!

Caddyman
06-02-2008, 04:04 PM
Tried this code as suggested
Sub RemoveHyperLinks2()
Dim lCount As Long
'Step in reverse so Excel doesn't get confused
For lCount = ActiveSheet.Hyperlinks.Count To 1 Step -1
ActiveSheet.Hyperlinks(lCount).Delete
Next
End Sub

But got the same error, this time the debugger stops on this line
For lCount = ActiveSheet.Hyperlinks.Count To 1 Step -1
Is there a maximum number of objects that Activesheet can handle?? I ask this because the following also gets the same error message on a large sheet, but work well on small numbers.
Sub RemovePictures()
'Selects all Pictures on the active sheet and deletes them
Dim DrObj
Dim Pict
Set DrObj = ActiveSheet.DrawingObjects
For Each Pict In DrObj
DrObj.Delete
Next
End SubSub RemomveControls()
Dim obj As OLEObject
'Selects all ActiveX controls on the active sheet and deletes them
For Each obj In ActiveSheet.OLEObjects
obj.Delete
Next obj
End Sub

xld
06-02-2008, 04:32 PM
If you hover this bit of code

ActiveSheet.Hyperlinks.Count

what do you get?

Caddyman
06-02-2008, 05:58 PM
If you hover this bit of code

ActiveSheet.Hyperlinks.Count

what do you get?
Nothing???? the only part thats show up when i hover is lcount=0

Jan Karel Pieterse
06-02-2008, 09:04 PM
What about thisone:

Sub RemoveLinksTheHardWay()
On Error Resume Next
Do While Err.Number = 0
ActiveSheet.Hyperlinks(1).Delete
Loop
End Sub

If this one fails too, try if your problem goes away on a freshly inserted worksheet.