Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 * |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
setup help/excel wk/sheet to record museum library collection | New Users to Excel | |||
Find exact match, same workbook, different sheets | Excel Discussion (Misc queries) | |||
type in one sheet and appear on all sheets | New Users to Excel | |||
Find / Replace sheets vs workbook in VB | Excel Worksheet Functions | |||
Looking to save one sheet in a workbook of two sheets... | Excel Programming |