Identifying a Pivot Table

Prev: Convert uppercase to lowercase
Next: Range created using Union...accessing cells

From: Shell on 22 Dec 2009 12:01

---

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
--
Shell

From: Dave Peterson on 22 Dec 2009 12:10

---

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



Shell wrote:
>
> 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
> --
> Shell

--

Dave Peterson

From: Jim Thomlinson on 22 Dec 2009 12:13

---

dim pvt as pivottable

for each pvt in activesheet.pivottables
msgbox pvt.name
next pvt
--
HTH...

Jim Thomlinson


"Shell" wrote:

> 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
> --
> Shell

From: minimaster on 22 Dec 2009 17:06

---

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

 | 
Pages: 1
Prev: Convert uppercase to lowercase
Next: Range created using Union...accessing cells