Active Sheet Error Message

[George]

I'm getting the error message below from VBA from the following code on Line
2

Debug.Print ActiveWorkbook.Name
Debug.Print ActiveWorkbook.ActiveSheet

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

Automation Error
Element not found.



I added ActiveWorkbook as a Watch Expression in the Watch window, and I can
click on the + and I can see an entry as a child record for ActiveSheet. I
can click on the ActiveSheet entry and see the name of the ActiveSheet. The
element clearly is there in the Watch Window but not seen in the program.

Why is VB telling me the Automation Object can't be found?

 

[Gary''s Student]

Debug.Print ActiveWorkbook.ActiveSheet.Name

 

[George]

I get the same message on the line of code you suggested which is what I
already had previously

 

[Gary''s Student]

Hi George:

Put your lines of code in a sub in a standard module and try them there

 

[George]

This is what my entire Sub looks like, its failing on line 2 as described
before

Sub DelShapesOnSht()
Dim shp As Shape
Debug.Print ActiveWorkbook.Name
Debug.Print ActiveWorkbook.ActiveSheet.Name

Debug.Print ActiveWorkbook.ActiveSheet.Shapes.Count
If ActiveWorkbook.ActiveSheet.Shapes.Count > 0 Then
For Each shp In ActiveWorkbook.ActiveSheet.Shapes
shp.Delete 'Delete the shape
Next shp
End If
End Sub

 

[Gary''s Student]

I pasted your code (as posted) into a standard module on my computer and it
ran without problems. It even deleted a Shape.

 

[Dave Peterson]

If you create a test workbook and add some shapes, does your code work ok?

I'd guess that your workbook is corrupted some way.

Maybe you activate a different sheet in your real workbook and test it there.
Maybe it's just that single sheet that's a problem.

 

[George]

That was a good suggestion. I was running the macro against a large sheet
that had been cut and pasted from a web site. I started over with a New
workbook, pasted a smaller number of rows and it ran fine.

I'm running Excel 2000. I'm guessing that there was some kind of memory
overlap problem with the extra large number of row. Very strange behavior.

The first sheet I was using had 528 rows, the 2nd time I ran the sheet had
about 324. That seems very odd.

Both data sets are from the same web site, just a smaller number of rows,
same headers and footers.

 

[Dave Peterson]

Maybe you can delete the old worksheet and recreate it from the web page?

 

[George]

That's what I wound up doing. Created new worksheet and then pasted in the
smaller number of rows, and my macro ran fine to completion.