How to add a column as Filter and Table desing format (ex:pivot styple light 4) when using VB.net code to create pivot table

[rajap]

Hello,

I am using the following VB Code to create the pivot table.
could you please tell me

1. How to add a column to the pivot table filter.
2. Is there a way to create the pivot table using a table design format such as “Pivot Style Light 4”

Code I am using:
'Create the excel objects to work on
Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim ws As Microsoft.Office.Interop.Excel.Worksheet
Dim p_ws As Microsoft.Office.Interop.Excel.Worksheet
Dim rng As Microsoft.Office.Interop.Excel.Range
Dim p_rng As Microsoft.Office.Interop.Excel.Range
Dim pt As Microsoft.Office.Interop.Excel.PivotTable

excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb = excel.Workbooks.Open(FileName)
excel.Visible=False

ws=CType(wb.Sheets(TargetSheet),Microsoft.Office.Interop.Excel.Worksheet)
p_ws=CType(wb.Sheets.Add,Microsoft.Office.Interop.Excel.Worksheet)
rng=CType(ws.Range(SourceRange),Microsoft.Office.Interop.Excel.Range)
‘starting cell of pivot table data
p_rng= CType(p_ws.Cells(6,1),Microsoft.Office.Interop.Excel.Range)
p_ws.Name=pivotSheet’“PivotSheet”

'Create the Pivot Table cache and Pivot Table objects
Dim oPivotCache As Microsoft.Office.Interop.Excel.PivotCache=Nothing
Dim oPivotTables As Microsoft.Office.Interop.Excel.PivotTables=Nothing
Dim oPivotTable As Microsoft.Office.Interop.Excel.PivotTable=Nothing

oPivotCache=CType(wb.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase,rng),Microsoft.Office.Interop.Excel.PivotCache)
oPivotTables=DirectCast(p_ws.PivotTables(Type.Missing),Microsoft.Office.Interop.Excel.PivotTables)
oPivotTable=oPivotTables.Add(oPivotCache,p_rng,“SourceDataForPivotTable”,Type.Missing,Type.Missing) 'Provide the PivotTable name

‘Creation of pivot Fields’
Dim oPivotField As Microsoft.Office.Interop.Excel.PivotField=Nothing
Dim oPivotValues As Microsoft.Office.Interop.Excel.PivotValueCell=Nothing

'*** create pivot table rows
oPivotField=CType(oPivotTable.PivotFields(“EMP_NAME”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the column name to move to the rows’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField

oPivotField=CType(oPivotTable.PivotFields(“STATUS”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the column name to move to the columns’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField

oPivotField=CType(oPivotTable.PivotFields(“STATUS”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the column name to move to the values’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
oPivotField.Function=Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlCount
oPivotField.Name=“Count of STATUS”

'Save and Close Workbook
wb.Save
wb.Close

[Cristian_Negulescu]

Hello,
In this video I create Pivot Table from VBA:

How to create a PivotTable in Excel from UiPath using VBA | UiPath Excel Pivot

Here is the Code:

cristinegulescu/startUiPathFromSalesforce/blob/master/VBAFile.vbs

Thanks,
Cristian