How to find the type of Sheet in Excel.Workbook.sheets collection
 

Hi,

How can I find out what type of sheet ( Chart, Dialog, worksheet ) is
present in Excel workbook.Sheets collection. I need to perform two
different operations based on the type.

Thanks,

How to find the type of Sheet in Excel.Workbook.sheets collection
 

Raj,

The obvious way is to check the sheets type property, but that deoesn't work
even though there are a whole set of type constants.

The previous post from Rob Bovey gives a safer method

'-------------------------

The Type property is only useful for distinguishing between regular
worksheets and the two types of XLM macro sheets. I don't really know why
they have constants in there for Charts and DialogSheets, but they don't
serve any purpose that I know of (the DialogSheet object doesn't even have a
Type property, as you've discovered).

Instead, you could use the TypeName function to return the string which
identifies the sheet type, then when you hit a worksheet use the Type
property to determine what type of sheet it is:

Sub ListSheetTypes()
Dim objSheet As Object
Dim szType As String
For Each objSheet In ActiveWorkbook.Sheets
szType = TypeName(objSheet)
If szType = "Worksheet" Then
Select Case objSheet.Type
Case xlWorksheet
Debug.Print szType
Case xlExcel4MacroSheet
Debug.Print "Excel4MacroSheet"
Case xlExcel4IntlMacroSheet
Debug.Print "Excel4IntlMacroSheet"
End Select
Else
Debug.Print szType
End If
Next objSheet
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/


'--------------------------------------

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Raj" wrote in message
m...
Hi,

How can I find out what type of sheet ( Chart, Dialog, worksheet ) is
present in Excel workbook.Sheets collection. I need to perform two
different operations based on the type.

Thanks,

How to find the type of Sheet in Excel.Workbook.sheets collection
 

"Raj" wrote in message
m...
Hi,

How can I find out what type of sheet ( Chart, Dialog, worksheet ) is
present in Excel workbook.Sheets collection. I need to perform two
different operations based on the type.

Hi Raj,

Here's one way:

Dim objSheet As Object
For Each objSheet In ActiveWorkbook.Sheets
If TypeOf objSheet Is Excel.Worksheet Then
''' It's a worksheet.
ElseIf TypeOf objSheet Is Excel.Chart Then
''' It's a Chart sheet.
ElseIf TypeOf objSheet Is Excel.DialogSheet Then
''' It's a DialogSheet.
End If
Next objSheet

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

How to find the type of Sheet in Excel.Workbook.sheets collection
 

Sub SheetTypes()
Dim sh As Object
For Each sh In ActiveWorkbook.Sheets
Debug.Print TypeName(sh)
Next
End Sub

--

Vasant


"Raj" wrote in message
m...
Hi,

How can I find out what type of sheet ( Chart, Dialog, worksheet ) is
present in Excel workbook.Sheets collection. I need to perform two
different operations based on the type.

Thanks,