430 Class does not support automation
 

I have a module in my excel spreadsheet that has following code in it.

Sub GetPicture
Dim xlsheet as Excel.Worksheet
Set xlsheet = New Excel.Worksheet
....
....
Set xlsheet = nothing

End Sub

The objective is to create a new temporary worksheet within the Sub and
destroy it at the end of sub so that the other worksheets do not have to be
disutrbed. When I execute the first statement I get the following error.

Error num 430 Class does not support automation or does not support expected
interface.
I tried the statement Set xlsheet = CreateObject("Excel.Worksheet"), but
that fails too with the message "Acitvex object cannot create object"
Please help
-- M

430 Class does not support automation
 

Srinivas,

Would something like

Sheets.Add
Application.ActiveSheet.Name = "Temp_Name_Wont_Be_Taken"
....
....
Sheets("Temp_Name_Wont_Be_Taken").Delete

work for your situation???

Add's a new sheet, renames it (so it can be reffered to later more
easily and allows other sheets to be worked with) then deletes it.

Dan E

"Srinivas Chundi" wrote in message ...
I have a module in my excel spreadsheet that has following code in it.

Sub GetPicture
Dim xlsheet as Excel.Worksheet
Set xlsheet = New Excel.Worksheet
...
...
Set xlsheet = nothing

End Sub

The objective is to create a new temporary worksheet within the Sub and
destroy it at the end of sub so that the other worksheets do not have to be
disutrbed. When I execute the first statement I get the following error.

Error num 430 Class does not support automation or does not support expected
interface.
I tried the statement Set xlsheet = CreateObject("Excel.Worksheet"), but
that fails too with the message "Acitvex object cannot create object"
Please help
-- M

430 Class does not support automation
 

Use the Add method of the Worksheets object to create the new sheet. E.g.,

Set xlsheet = ThisWorkbook.Worksheets.Add


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Srinivas Chundi" wrote in message
...
I have a module in my excel spreadsheet that has following code in it.

Sub GetPicture
Dim xlsheet as Excel.Worksheet
Set xlsheet = New Excel.Worksheet
...
...
Set xlsheet = nothing

End Sub

The objective is to create a new temporary worksheet within the Sub and
destroy it at the end of sub so that the other worksheets do not have to
be
disutrbed. When I execute the first statement I get the following error.

Error num 430 Class does not support automation or does not support
expected
interface.
I tried the statement Set xlsheet = CreateObject("Excel.Worksheet"), but
that fails too with the message "Acitvex object cannot create object"
Please help
-- M