Regarding Excel Pivot using VB.NET code

Hi Team,

we are trying to create a pivot table using VB.NET code. it works good so far. but i have an small issue with Pivot field property called DragToColumn. it is not working if i use it in InvokeCode.

Getting below error:



Exception from HRESULT: 0x800A03EC

OK

Sample Code:

oPivotField=CType(oPivotTable.PivotFields(ValueCountField),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
oPivotField.Function=Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlCount
oPivotField.Name=“Count_Of_”&ValueCountField
''oPivotField.Label=""

oPivotField.DragToColumn= True

It means This property is not exists in Microsoft.Office.Interop.Excel.PivotField.

but when i search in google, it is there.

please advise your thought on this?

Thanks and Regards
Ramu Thirugnanam

@ramu_at,

Is there any specific reason you are assigning true value here?

The thing is that, by default , for all the columns in the table will have the value as TRUE. If we don’t want the column to be dragged to the column position, then we need to set it to false.

So, i hope if you comment out that line of code will make no change in your code.

By default, this column added into Pivot Rows. But i wanted to be in the Pivot Columns.

Then you need to add a piece of code after your code .

Here is the code after creating the data field,

CType(oPivotTable.PivotFields(“Count_Of_”&ValueCountField),Microsoft.Office.Interop.Excel.PivotField).Orientation = xlColumnField

Do this after creating the count data column.

Hope this works @ramu_at

it is not working. now this field no where showing

code:

oPivotField=CType(oPivotTable.PivotFields(ValueCountField),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
oPivotField.Function=Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlCount
oPivotField.Name=“Count_Of_”&ValueCountField
CType(oPivotTable.PivotFields(“Count_Of_”&ValueCountField),Microsoft.Office.Interop.Excel.PivotField).Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField

It is working for me .

Here is the code which I have tested

oPivotField = CType(oPivotTable.PivotFields("Foreign Amount"), Microsoft.Office.Interop.Excel.PivotField) 'Specfiy the coulmn name'
    oPivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
    oPivotField.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum
    'oPivotField.DragToColumn = True

    CType(oPivotTable.PivotFields("Foreign Amount"), Microsoft.Office.Interop.Excel.PivotField).Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField

yes this will work. can you please add one more column for count(no. of item) along with Sum?
i want both sum and count in the pivot column.

@ramu_at

Just copy and paste the same code and change the condition to xlCount and save the column as different name. Again the same procedure :slight_smile:

Thanks for quick reply. i think you have not tested above scenario.

can you create a sample code for the below scenario?

image

Pivout output for above is

That is not working @ramu_at, :slight_smile:

But without changing the orientation of the count row, and changing for sum also giving the values in column field…

I get like this if i add just sum and count field
image

and out put is

image

Can you try changing for count column leaving sum as it is?

but what i would like to have is

image

output is

as i mentioned above

no luck