How to create Pivot table with & without using Create Pivot Table actvity?

thanks , it worked for me

Hi Haroon,

My experience is if you want to do some analysis in excel, using Excel power query or power pivot to do it. or you can use Excel macro to record your action and use invoke VBA activity.

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