Hello,
I am using the following VB Code to create the pivot table.
could you please tell me
- How to add a column to the pivot table filter.
- 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