Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine If Pivot Table Exists
Hey,
I need to loop through all the worksheets in a workbook and for each worksheet with a pivot table I want to cleanup the pivot cache. I've done this for workbooks where all the worksheets have a pivot table but I'm having trouble doing this where some worksheets have a pivot table and some don't. My guess is I need to determine if a worksheet has a pivot table and if it does clean it up otherwise move on to the next spreadsheet. I'm converting existing VBA code to VB.NET 2005 so an example using either language would work for me. Thanks for the help! -- -ridawg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine If Pivot Table Exists
dim wks as worksheet
dim iCtr as long for each wks in activeworkbook.worksheets for iCtr = 1 to wks.pivottables.count next ictr next wks Or if you just want to check... if wks.pivottables.count 0 then 'it has at least one pt end if ridawg wrote: Hey, I need to loop through all the worksheets in a workbook and for each worksheet with a pivot table I want to cleanup the pivot cache. I've done this for workbooks where all the worksheets have a pivot table but I'm having trouble doing this where some worksheets have a pivot table and some don't. My guess is I need to determine if a worksheet has a pivot table and if it does clean it up otherwise move on to the next spreadsheet. I'm converting existing VBA code to VB.NET 2005 so an example using either language would work for me. Thanks for the help! -- -ridawg -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine If Pivot Table Exists
Maybe something like this?:
'--------Start of Code----------- Sub ListPvtTblSheets() Dim shtWS As Worksheet Dim pvtPT As PivotTable Dim strMsg As String strMsg = "" For Each shtWS In ThisWorkbook.Worksheets If shtWS.PivotTables.Count 0 Then strMsg = strMsg & shtWS.Name & vbCr For Each pvtPT In shtWS.PivotTables strMsg = strMsg & "-" & pvtPT.Name & vbCr Next pvtPT End If Next shtWS MsgBox strMsg End Sub '--------End of Code----------- Is that something you can work with? *********** Regards, Ron XL2002, WinXP "ridawg" wrote: Hey, I need to loop through all the worksheets in a workbook and for each worksheet with a pivot table I want to cleanup the pivot cache. I've done this for workbooks where all the worksheets have a pivot table but I'm having trouble doing this where some worksheets have a pivot table and some don't. My guess is I need to determine if a worksheet has a pivot table and if it does clean it up otherwise move on to the next spreadsheet. I'm converting existing VBA code to VB.NET 2005 so an example using either language would work for me. Thanks for the help! -- -ridawg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine If Pivot Table Exists
Dim sh as Worksheet, pt as PivotTable
for each sh in worksheets if sh.PivotTables.count 1 then for each pt in sh.PivotTables Next end if Next -- Regards, Tom Ogilvy "ridawg" wrote: Hey, I need to loop through all the worksheets in a workbook and for each worksheet with a pivot table I want to cleanup the pivot cache. I've done this for workbooks where all the worksheets have a pivot table but I'm having trouble doing this where some worksheets have a pivot table and some don't. My guess is I need to determine if a worksheet has a pivot table and if it does clean it up otherwise move on to the next spreadsheet. I'm converting existing VBA code to VB.NET 2005 so an example using either language would work for me. Thanks for the help! -- -ridawg |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine If Pivot Table Exists
Thanks for the help!
I was able to get this to work with a little trial error. Just what I needed. -- -ridawg "Dave Peterson" wrote: dim wks as worksheet dim iCtr as long for each wks in activeworkbook.worksheets for iCtr = 1 to wks.pivottables.count next ictr next wks Or if you just want to check... if wks.pivottables.count 0 then 'it has at least one pt end if ridawg wrote: Hey, I need to loop through all the worksheets in a workbook and for each worksheet with a pivot table I want to cleanup the pivot cache. I've done this for workbooks where all the worksheets have a pivot table but I'm having trouble doing this where some worksheets have a pivot table and some don't. My guess is I need to determine if a worksheet has a pivot table and if it does clean it up otherwise move on to the next spreadsheet. I'm converting existing VBA code to VB.NET 2005 so an example using either language would work for me. Thanks for the help! -- -ridawg -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
determine in datasource or pivot table | Excel Discussion (Misc queries) | |||
determine the name of the selected pivot table | Excel Discussion (Misc queries) | |||
VBA:: determine if UDF exists? | Excel Discussion (Misc queries) | |||
determine if value exists | Excel Programming | |||
If Pivot Table Exists | Excel Programming |