Create a pivot table

Help
7

@sshitol ,

There are two ways to create a pivot table in UiPath…

  1. Make the data in your excel in a tabular form and give the name to the table. Use pivot table activity and provide that name and sheet in that so that it will form the entire data to pivot.

or

  1. You need to write a piece of code to make the data into pivot.

Here is the code to copy two rows of data into new sheet and making a pivot of that data :slight_smile:

  Dim excel As Microsoft.Office.Interop.Excel.Application
    Dim wb As Microsoft.Office.Interop.Excel.Workbook
    Dim ws As Microsoft.Office.Interop.Excel.Worksheet
    excel = New Microsoft.Office.Interop.Excel.Application
    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
    Dim oPivotField As Microsoft.Office.Interop.Excel.PivotField = Nothing
    Dim rng As Microsoft.Office.Interop.Excel.Range 'capturing the range of qry_GS_Monthly_ProjRes '''
    Dim p_rng As Microsoft.Office.Interop.Excel.Range 'capturing the range of pivottable sheet1''
    Dim s1 As Microsoft.Office.Interop.Excel.PivotItems = Nothing

    wb = excel.Workbooks.Open("") 'Open the excel the file' 'Open the excel the file'
    excel.Visible = True
    ws = CType(wb.Sheets("MT"), Microsoft.Office.Interop.Excel.Worksheet) 'select a sheet and activiates'
    ws.Activate()


    Dim newSheet As Microsoft.Office.Interop.Excel.Worksheet = Nothing

    newSheet = CType(wb.Sheets.Add, Microsoft.Office.Interop.Excel.Worksheet)
    newSheet.Name = "Pivot"
    ws.Activate()

    

    ws.Range("A:A").Copy()
    newSheet.Activate()
    newSheet.Range("A1").PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues)
    ws.Activate()
    ws.Range("C:C").Copy()
    newSheet.Activate()
    newSheet.Range("B1").PasteSpecial()
    ws.Activate()
            

    rng = ws.UsedRange
    p_rng = CType(newSheet.Cells(1, 2), Microsoft.Office.Interop.Excel.Range)

    oPivotCache = CType(wb.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, rng), Microsoft.Office.Interop.Excel.PivotCache)
    oPivotTables = DirectCast(newSheet.PivotTables(Type.Missing), Microsoft.Office.Interop.Excel.PivotTables)
    oPivotTable = oPivotTables.Add(oPivotCache, p_rng, "Summary", Type.Missing, Type.Missing) 'first paramenter is cache,range the table should start,table name'

    'Creation of pivot Fields'



    oPivotField = CType(oPivotTable.PivotFields("Resources"), Microsoft.Office.Interop.Excel.PivotField) 'Specfiy the coulmn name'
    oPivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField
    oPivotField.LayoutForm = Microsoft.Office.Interop.Excel.XlLayoutFormType.xlTabular
    oPivotField.RepeatLabels = True

    oPivotField.Subtotals(1) = False

    oPivotField = CType(oPivotTable.PivotFields("Category"), Microsoft.Office.Interop.Excel.PivotField) 'Specfiy the coulmn name'
    oPivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField

    oPivotField = CType(oPivotTable.PivotFields("Resources"), 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 = CType(oPivotTable.PivotFields("Category"), 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

    wb.Save()
    wb.Close()
    excel.Quit()
1 Like