• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2445
  • Last Modified:

Microsoft, Excel, PivotTable, C#, VBA

PivotTable Column Headers' cells

Hi Experts,

I'm trying to solve a problem where I create a pivotTable with a few columns and need to adjust the size of the cells those column headers go to.  Issue is I am not able to reach individual cells of these column headers while I'm creating the column headers as a data field.

I add a data field to pivotTable like this: //code is C#, interop with MS Excel classes
for(int i=0, i<3, i++)
{
PivotField pivotField  = (pivotField) pivotTable.AddDataField(pivotTable.CubeFields[pivotFieldName], caption, Missing.Value]  

/*now I want to access that particular cell that the header (caption) was added to, and..
pivotField.DataRange gives the pivotField cell and not the column header cell
pivotField.ColumnWidth doesn;t exist
pivotField.Cells doesn;t exist
pivotField.CubeField gives CubeField's data and not Excel's cell/range
*/

Essentially, what I need is to reach column header cells through pivotField cell that I have

Any solutions in VB or C# are greatly appreciated.

Thanks


0
scrataz
Asked:
scrataz
  • 3
  • 3
1 Solution
 
Rory ArchibaldCommented:
Hi,
PivotField.LabelRange is what you want, I believe.
Regards,
Rory
0
 
scratazAuthor Commented:
PivotField.LabelRange is the same as pivotTable.DataRange - same 1 pivotField 'cell '

Any other possible hint?
0
 
Rory ArchibaldCommented:
It shouldn't be - the label range of a data field should return the cell at the top left corner of the pivot table (exluding the page header area). It must also be a very small pivot table if the pivot fields data range consists of one cell?
Regards,
Rory
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Rory ArchibaldCommented:
I should mention, I am basing this on VBA - haven't tested to see if the Interop assemblies return what they should!
Rory
0
 
scratazAuthor Commented:
HI Rory,

I indeed needed LabelRange. I had tried LabelRange before but my code was doing pivotTable.ManualUpdate = true; which meant that for every subsequent (from 1st onwards) pivotField in the pivotFields threw an exception on LabelRange.

Thanks
0
 
scratazAuthor Commented:
Now the situation is that LabelRange is selecting more than one cell if Label exists in more than one column. For ex. I say select currentPivotField's (named "Value") laberange and it selects all the columns in sheet and shows CloumnWidth for that LabelRange as null. Is theer a way I can get their ColumnWidth in case PivotField repeats itself on the sheet?

Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now