Excel sheet with macro Type Mismatch #13

[snehal]

Dear All,
I open a sheet which actually has macro and also filename.xls an
filename.xla
Now when i say
Msgbox excel.Application.Version 'it does it fine
Msgbox excel.activesheet.name 'it works fine
But when i say
Set Oworksheet = excel.activesheet ' it gives me typemismatch erro
#13
also the same error for the following
For Each sht In excel.ActiveWorkbook.Sheets ' gives error on this line
MsgBox sht.Name
Next sht

Any inputs?
Thanks a lot
sneha

 

[Tom Ogilvy]

ActiveSheet doesn't necessarily refer to a worksheet. If you dimmed you
variable as worksheet

Dim oWorksheet as Worksheet
Dim sht as Worksheet

then trying to assign it to other than a worksheet will cause that type of
problem.

For each sht in ActiveWorkbook.Worksheets
msgbox sht.name
Next

 

[snehal]

I have dimed the variables as Excel.Worksheet

The thing is it works fine with other sheets. But it just create
problems with this one sheet which has macros.
If you want i can email you the two xls file but you will have to chec
for virus just in case...

thanks
sneha

 

[Tom Ogilvy]

I have no interest in looking at them. Perhaps it is a macro sheet rather
than a worksheet (since you say it has macros).

Dim the variables as

Dim varname as Object