Active Sheet Error Message
 

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?

Active Sheet Error Message
 

Debug.Print ActiveWorkbook.ActiveSheet.Name
--
Gary''s Student - gsnu200812

Active Sheet Error Message
 

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


"Gary''s Student" wrote in message
...
Debug.Print ActiveWorkbook.ActiveSheet.Name
--
Gary''s Student - gsnu200812

Active Sheet Error Message
 

Hi George:

Put your lines of code in a sub in a standard module and try them there
--
Gary''s Student - gsnu200812


"George" wrote:

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


"Gary''s Student" wrote in message
...
Debug.Print ActiveWorkbook.ActiveSheet.Name
--
Gary''s Student - gsnu200812

Active Sheet Error Message
 

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" wrote in message
...
Hi George:

Put your lines of code in a sub in a standard module and try them there
--
Gary''s Student - gsnu200812


"George" wrote:

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


"Gary''s Student" wrote in
message
...
Debug.Print ActiveWorkbook.ActiveSheet.Name
--
Gary''s Student - gsnu200812

Active Sheet Error Message
 

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


"George" wrote:

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" wrote in message
...
Hi George:

Put your lines of code in a sub in a standard module and try them there
--
Gary''s Student - gsnu200812


"George" wrote:

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


"Gary''s Student" wrote in
message
...
Debug.Print ActiveWorkbook.ActiveSheet.Name
--
Gary''s Student - gsnu200812

Active Sheet Error Message
 

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 wrote:

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" wrote in message
...
Hi George:

Put your lines of code in a sub in a standard module and try them there
--
Gary''s Student - gsnu200812


"George" wrote:

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


"Gary''s Student" wrote in
message
...
Debug.Print ActiveWorkbook.ActiveSheet.Name
--
Gary''s Student - gsnu200812




--

Dave Peterson

Active Sheet Error Message
 

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" wrote in message
...
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 wrote:

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" wrote in
message
...
Hi George:

Put your lines of code in a sub in a standard module and try them there
--
Gary''s Student - gsnu200812


"George" wrote:

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


"Gary''s Student" wrote in
message
...
Debug.Print ActiveWorkbook.ActiveSheet.Name
--
Gary''s Student - gsnu200812




--

Dave Peterson

Active Sheet Error Message
 

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

George wrote:

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" wrote in message
...
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 wrote:

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" wrote in
message
...
Hi George:

Put your lines of code in a sub in a standard module and try them there
--
Gary''s Student - gsnu200812


"George" wrote:

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


"Gary''s Student" wrote in
message
...
Debug.Print ActiveWorkbook.ActiveSheet.Name
--
Gary''s Student - gsnu200812




--

Dave Peterson

--

Dave Peterson

Active Sheet Error Message
 

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.


"Dave Peterson" wrote in message
...
Maybe you can delete the old worksheet and recreate it from the web page?

George wrote:

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" wrote in message
...
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 wrote:

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" wrote in
message
...
Hi George:

Put your lines of code in a sub in a standard module and try them
there
--
Gary''s Student - gsnu200812


"George" wrote:

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


"Gary''s Student" wrote in
message
...
Debug.Print ActiveWorkbook.ActiveSheet.Name
--
Gary''s Student - gsnu200812




--

Dave Peterson

--

Dave Peterson