Pivot table macro error with OLAP

[kwhite100]

Hello everyone,

I have been struggling trying to handle an error that I am receiving. I am using a tool on the internet to analyze some data and am getting a pivot table as a result of the analyzer. I export this pivot table to an excel sheet and my goal is to run a macro to select various elements within the filters of the pivot table. The excel sheet connects to an external data source, which may be why I am receiving the error, "Run Time error '1004': Unable to get the PivotFields property of the WORKSHEET class."

Please note that I am using OLAP to connect to an existing data source. I think this may be what is causing me problems.

Here is my code

[FONT=Calibri][SIZE=3]Sub Armeda_Pivot()[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]cntItem = 0[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim arrVisibleItems[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]arrVisibleItems = Array("BERRYPET8144001") ' Fill in the items you want to display in pivot[/SIZE][/FONT]
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
[FONT=Calibri][SIZE=3]For Each pvtRowItem In ActiveSheet.PivotTables("PivotTable1").PivotFields("Project").PivotItems[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]' update the pivot name and row field name[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]pvtRowItem.Visible = True[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]cntItem = cntItem + 1[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Next pvtRowItem[/SIZE][/FONT]
<o:p></o:p>
<o:p></o:p>
[FONT=Calibri][SIZE=3]If cntItem < UBound(arrVisibleItems) Then[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]MsgBox "array has more items than listed in pivot"[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Exit Sub[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]End If[/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3]For Each pvtRowItem In ActiveSheet.PivotTables("PivotTable1").PivotFields("Project").PivotItems[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]If Not (arrSearch(pvtRowItem.Value, arrVisibleItems)) Then[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]pvtRowItem.Visible = False[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]End If[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Next pvtRowItem[/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3]Public Function arrSearch(strSearch As String, arrStrToBeSearched As Variant) As Boolean[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]For i = 0 To UBound(arrStrToBeSearched)[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]If strSearch = arrStrToBeSearched(i) Then[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]arrSearch = True[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Exit Function[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]End If[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Next i[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]arrSearch = False[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]End Function[/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3]End Function[/SIZE][/FONT]

Thanks!

 

[kwhite100]

Sorry All! I tried searching for OLAP questions on this forum and found this!

http://www.mrexcel.com/forum/showthread.php?t=453513

I searched for several other messages and none seemed to address them. It looks like this link to this other thread is relevant!

 

[Jerry Sullivan]

Hi kwhite100,

Are you filtering for one item or multiple items?
What area of the report are you filtering (Pagefield/Rowfield/ColumnField or DataField)?