430 Class does not support automation

[Srinivas Chundi]

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

 

[Dan E]

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

 

[Chip Pearson]

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 (e-mail address removed)