Drilling for Cells...

[RayTheOtherRay]

I have a PivotTable object, generated from saving an Excel PivotTable
as a web page. I am passing a cookie from another web page that
references a date from that pages date dimension. When the current
page opens, I get that cookie and subtract one day from its value so
that it will correspond to the dates in the current PivotTable date
dimension. In most cases, the two dimensions will be only one day
apart...however this is not always the case.

What I would like to do is subtract the day and apply that value to the
appropriate page filter on the current PivotTable. If the PivotTable
has no resulting values, then I'll loop and subtract another day and
check again. This loop will look within the tolerance of one week.
Beyond this, I start matching with the previous week's data.

I believe that by finding the Grand Total row, and checking the value
in the first column after the label column on the left, I can determine
if the table is empty or populated. My problem is coming up with the
proper string of objects and properties that lead to that cell. I've
been approaching it from a RowAxis standpoint and have been scouring
the OWCVBA11.chm file. I'd appreciate anyone with a quick explanation
to clear the fog for me.

Thanks!
RayTheOtherRay <><

 

[RayTheOtherRay]

In my searching, this morning, I have found the following answer...

// PivotTable object
var pt = document.getElementById('My_PivotTable');
// PivotData object
var pd = pt.ActiveData;
// RowMember for use as input to get PivotCell object
var rm = pd.RowAxis.RowMember;
// ColumnMember for use as input to get PivotCell object
var cm = pd.ColumnAxis.ColumnMember;
// PivotCell object
var pc = pd.Cells(rm, cm)
// Grand Total of first aggregate column
var gt = pc.Aggregates(0).Value;

This seems rather convoluted, but does give me a figure that I can
compare to 0. I those of you with more experience and shed light on a
more elegant solution, I stand ready to improve my code.

Thanks!
Ray <><

 

[RayTheOtherRay]

If I could only type...the last paragraph should have read...

This seems rather convoluted, but does give me a figure that I can
compare to 0. If those of you with more experience can shed light on a
more elegant solution, I stand ready to improve my code.

Thanks again!
RayTheOtherRay <><