B. Clay Shannon B. Clay Shannon - 2 months ago 4
C# Question

Why is my PivotTable field name considered to be invalid and/or my data viewed as not organized as a list with labeled columns?

Note: I have a related question here, but have completely changed the code and still get the same err msg, namely, "The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field."

As you can see in the code below, the Pivot Table field is being given names (first "Description" and then "Months"):

private void AddPivotTable()
{
var pch = _xlBook.PivotCaches();
pch.Add(XlPivotTableSourceType.xlDatabase, "A6:F94")
.CreatePivotTable(_xlSheet.Cells[6, 1], "PivTab1", Type.Missing, Type.Missing);

var pvt = _xlSheet.PivotTables("PivTab1") as PivotTable;
if (pvt != null)
{
pvt.MergeLabels = true;
pvt.ErrorString = "";
pvt.DisplayErrorString = true;

var fld = ((PivotField)pvt.PivotFields("Description"));
fld.Orientation = XlPivotFieldOrientation.xlRowField;

fld = ((PivotField)pvt.PivotFields("Month"));
fld.Orientation = XlPivotFieldOrientation.xlColumnField;
fld.NumberFormat = "MMM yy";
fld.DataRange.Interior.Color = ColorTranslator.ToOle(Color.LightBlue);
}
}


I thought maybe the problem was that the same PivotField ("fld") was being used for both the "Description" row field and the "Month" column field, so I refactored the code to this:

private void AddPivotTable()
{
var pch = _xlBook.PivotCaches();
pch.Add(XlPivotTableSourceType.xlDatabase, "A6:F94")
.CreatePivotTable(_xlSheet.Cells[6, 1], "PivTab1", Type.Missing, Type.Missing);

var pvt = _xlSheet.PivotTables("PivTab1") as PivotTable;
if (pvt != null)
{
pvt.MergeLabels = true;
pvt.ErrorString = "";
pvt.DisplayErrorString = true;

var descriptionPivotField = (PivotField)pvt.PivotFields("Description");
descriptionPivotField.Orientation = XlPivotFieldOrientation.xlRowField;

var monthPivotField = (PivotField)pvt.PivotFields("Month");
monthPivotField.Orientation = XlPivotFieldOrientation.xlColumnField;
monthPivotField.NumberFormat = "MMM yy";
monthPivotField.DataRange.Interior.Color = ColorTranslator.ToOle(Color.LightBlue);
}
}


...but it made no difference; it still tells me the PivotTable field name is not valid - but which one, "Description" or "Month"? Both? Why? ISTM that either the err msg is misleading or the name requirements are quite odd.

When the code works and the sheet is generated, it should look like this, with "Month" and "Description" PivotTable fields:

enter image description here

Here is how my generated sheet currently looks, though, without the PivotTable or its fields (this is what is generated when the PivotTable code is commented out):

enter image description here

A possibile true cause of the problem can be related to the second part of the err msg, namely, "...you must use data that is organized as a list with labeled columns." But as you can see in the screen shot directly above, there is data beginning at A6 (which is the first column of the header row) and does continue through column F and line 94 with the test data:

enter image description here

(actually, the data continues through line 1570 before the Totals rows take over).

The complete err msg I receive is:

The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.
Exception Source: Microsoft Office Excel
Exception StackTrace: at System.Dynamic.ComRuntimeHelpers.CheckThrowException(Int32 hresult, ExcepInfo&
excepInfo, UInt32 argErr, String message)
at CallSite.Target(Closure , CallSite , ComObject , Object , String , Object , Object )
at System.Dynamic.UpdateDelegates.UpdateAndExecute5[T0,T1,T2,T3,T4,TRet](CallSite site, T0 arg0, T1
arg1, T2 arg2, T3 arg3, T4 arg4)
at CallSite.Target(Closure , CallSite , PivotCache , Object , String , Object , Object )
at System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid5[T0,T1,T2,T3,T4](CallSite site, T0 arg0, T1 arg1,
T2 arg2, T3 arg3, T4 arg4)
at ReportRunner.ProduceUsage.ProduceUsageRpt.AddPivotTable() in c:\Projects\ReportRunner\ReportRunner
\ProduceUsage\ProduceUsageRpt.cs:line 3195
at ReportRunner.ProduceUsage.ProduceUsageRpt.GenerateProduceUsageRpt() in c:\Projects\ReportRunner
\ReportRunner\ProduceUsage\ProduceUsageRpt.cs:line 192


Line 3195 is this one:

pch.Add(XlPivotTableSourceType.xlDatabase, "A6:F94")
.CreatePivotTable(_xlSheet.Cells[6, 1], "PivTab1", Type.Missing, Type.Missing);


Line 192 is the call to AddPivotTable()

UPDATE



cyboashu's answer sounds promising, but I don't know if the "Sheet1" in his example is the name of the sheet object or the sheet name.

Here is my code:

private Worksheet _xlSheet;

private static readonly String ProduceUsageByMonthSheetName = "Produce Usage by Month";
_xlSheet.Name = ProduceUsageByMonthSheetName;


...and I tried every permutation I could think of:

//pch.Add(XlPivotTableSourceType.xlDatabase, _xlSheet.Range("A6:F94")) //"PivotData!A1:H" + rowIdx)
//pch.Add(XlPivotTableSourceType.xlDatabase, _xlSheet!"A6:F94")
//pch.Add(XlPivotTableSourceType.xlDatabase, ProduceUsageByMonthSheetName!"A6:F94")
//pch.Add(XlPivotTableSourceType.xlDatabase, ProduceUsageByMonthSheetName.Range("A6:F94"))
//pch.Add(XlPivotTableSourceType.xlDatabase, "Produce Usage by Month"!"A6:F94")
pch.Add(XlPivotTableSourceType.xlDatabase, "Produce Usage by Month".Range("A6:F94"))
.CreatePivotTable(_xlSheet.Cells[6, 1], "PivTab1", Type.Missing, Type.Missing);


...yet they all fail to compile:

Answer

You are not supplying full qualified range address.

.Add(XlPivotTableSourceType.xlDatabase, "A6:F94")
                .CreatePivotTable(_xlSheet.Cells[6, 1], "PivTab1", Type.Missing, Type.Missing);

Change "A6:F94" to include sheet name such as Sheet1!"A6:F94" or Sheet1.Range("A6:F94"")

It will rectify the error.Error messages in Excel-Object Model are bit confusing.

Upadte

Your are mixing it. If the sheet name is Produce Usage by Month" then instead of "Produce Usage by Month".Range("A6:F94")) use "Produce Usage by Month!A6:F94"

Tip; in Excel record a macro to see how the syntax works.

Comments