Synchronise pivot table with filtered list table as source (Excel automation)

[mikeopolo]

Hi, I'm attempting to create a pivot table using a list table as the datasource, and if I filter the source table, then I would like the pivot table to reflect the same filter. My thinking is that the source range is defined as visible cells only.

Code fragments:

Create dynamic range (to accommodate changes in the amount of data being placed on the worksheet):

WITH loworkbook.ActiveSheet
[indent]* lnFCount gives number of columns in the data range
lncols = lnFCount - 1
*include column headings
.Names.Add("data", "=OFFSET(Data!$a$1,0,0,COUNTA(Data!$A:$A),"+TRANSFORM(lncols+1)+")")
[/indent]
ENDWITH

Create table from (dynamic) range:

WITH loworkbook.ActiveSheet
[indent].ListObjects.Add(xlSrcRange, .Range("data!data"), , xlYes).Name = "Table1"
[/indent]
ENDWITH 
loSet = losheet.ListObjects("Table1")

Create pivot table:

WITH loExcel.ActiveWorkbook
[indent]* loset.range refers to the entire list table
.PivotCaches.Create(xlDatabase,loset.range.specialcells(xlvisible),xlPivotTableVersion10);
.CreatePivotTable("Pivot!R7C1","PivotTable",xlPivotTableVersion10)	&& Destination ref (must use r1c1 ref style), PT name, version
[/indent]
ENDWITH

Using "specialcells(xlvisible)" is my attempt to achieve my objective, but although the pivot table is created successfully, when I filter the source table, the pivot table does not change on refresh.

Can anyone suggest a way forward (or sideways)?

Mike

 

[JRB-Bldr]

My way to figure out how and what VFP Automation needs to do in Excel begins by not utilizing VFP at all.

I first begin by doing what I want solely in Excel while recording the actions as a Macro.
Then, when complete, I can analyze the Macro's VBA code and see how it was done.

Finally I can then go into VFP and write the VFP Automation code to do the same thing.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

I'd say without trying to validate the assumption the PivotCaches.Create method at max is making a dynamic source of the range cells visible at the time of that call, no matter how you set the visibility afterwards.

I know the Graphs you create in Excel react to value changes in the range they are bound to, so I'd expect you get changes in the pivot table when changing values in the range, but you don't get a change of the range, when you set cells invisible or visible. That kind of dynamics is one level too much asked.

Bye, Olaf.

 

[mikeopolo]

Thanks for both helpful replies. I also think my plan is beyond the design capabilities of the pivotcache.

My current thinking is to:
- ignore specialcells etc. when creating the pivot table
- use VBA code built in to the target spreadsheet, linked to a "Refresh" button
- copy the filtered range to a recordset, ie excluding visible cells
- update the pivotcache from that recordset.

But I'll have to head over to the VBA forum, as selecting the filtered range gives me either 1 row of headings or all data, so far.

Kind regards
Mike