ramu_at
(Ramu At)
June 21, 2019, 9:08am
1
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
HareeshMR
(Hareesh Madasi)
June 21, 2019, 9:24am
2
@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.
ramu_at
(Ramu At)
June 21, 2019, 9:37am
3
By default, this column added into Pivot Rows. But i wanted to be in the Pivot Columns.
HareeshMR
(Hareesh Madasi)
June 21, 2019, 9:50am
4
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
ramu_at
(Ramu At)
June 21, 2019, 10:03am
5
it is not working. now this field no where showing
ramu_at
(Ramu At)
June 21, 2019, 10:04am
6
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
HareeshMR
(Hareesh Madasi)
June 21, 2019, 10:28am
7
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
ramu_at
(Ramu At)
June 21, 2019, 11:02am
8
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.
HareeshMR
(Hareesh Madasi)
June 21, 2019, 11:07am
9
@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
ramu_at
(Ramu At)
June 21, 2019, 11:26am
10
Thanks for quick reply. i think you have not tested above scenario.
can you create a sample code for the below scenario?
ramu_at
(Ramu At)
June 21, 2019, 11:27am
11
Pivout output for above is
HareeshMR
(Hareesh Madasi)
June 21, 2019, 11:46am
12
That is not working @ramu_at ,
But without changing the orientation of the count row, and changing for sum also giving the values in column field…
ramu_at
(Ramu At)
June 21, 2019, 12:00pm
13
I get like this if i add just sum and count field
and out put is
HareeshMR
(Hareesh Madasi)
June 21, 2019, 12:01pm
14
Can you try changing for count column leaving sum as it is?
ramu_at
(Ramu At)
June 21, 2019, 12:02pm
15
but what i would like to have is
output is
as i mentioned above
Mohanasaii
(Mohanaselvi)
October 7, 2020, 9:29am
17
Hi. I am also facing the same Issue.
Have you found any solution for this. Please help.