Identifying a Pivot Table

  • Thread starter Thread starter Shell
  • Start date Start date
S

Shell

How can I tell in code, I the current tab contains a pivot table.

Actually, I need the code for Access, But if I can see the code in an Excel
Macro I can translate it to Access VBA. (Hopefully)

Thanks
 
One way:

Option Explicit
Sub testme()

Dim wks As Worksheet

Set wks = ActiveSheet

If wks.PivotTables.Count > 0 Then
MsgBox "yep"
Else
MsgBox "nope"
End If

End Sub
 
dim pvt as pivottable

for each pvt in activesheet.pivottables
msgbox pvt.name
next pvt
 
I use this function in my pivot tools to see whether there is any
pivot table to work with.

Function getPivotTable() As PivotTable
Dim ch As ChartObject
On Error Resume Next

Set ch = ActiveChart
If Not ch Is Nothing Then
Set getPivotTable = ActiveChart.PivotLayout.PivotTable ' 1st
will see whether we have an active pivot chart
If Not getPivotTable Is Nothing Then Exit Function
End If

Set getPivotTable = ActiveCell.PivotTable ' 2nd will see whether
there is an active pivot table
If Not getPivotTable Is Nothing Then Exit Function

Set getPivotTable = ActiveSheet.PivotTables(1) ' lets see
whether there is at least one pivot table on the sheet
If Not getPivotTable Is Nothing Then Exit Function

If ActiveSheet.ChartObjects.Count > 0 Then ' or an
embedded pivot chart
For Each ch In ActiveSheet.ChartObjects
If ch.Chart.HasPivotFields Then
Set getPivotTable = ch.Chart.PivotLayout.PivotTable
Exit For
Else
Set getPivotTable = Nothing
End If
Next ch
Else ' done our best to find the pivot
table that we can work with
Set getPivotTable = Nothing ' this should trigger an error
message in the caller now.
End If

End Function

' for testing this should work roughly
sub test()
dim pt as pivottable
set pt=getpivottable()
if pt is nothing then
msgbox "Goto sheet with a pivot table and try again!"
exit sub
end if
end sub
 
Back
Top