Create a pivot table

Hi Team,

I want to create a pivot table for attached excel using pivot table activity.
I have created manually, for “sheet 3” i have created pivot table in “sheet 4” and for “sheet 1” i have created pivot table in “sheet 2”.
Now I have to create using BOT. pivot.xlsx (52.7 KB)

Regards,
Sushant

Hi

You can write a Macro code and invoke that using invoke VBA activity inside excel application Scope.

Hi @vishnuvarthanp,
I dont know much about macro. Can you help me in this.

Regards,
Sushant

Hi @sshitol

I’m via my mobile, so right now unable to check the excel. But I thought of sharing two components which might help.


This includes a data transpose activity which might help to transpose your excel data (row to column and column to row)


This includes another activity which performs consolidation of data tables. Like a group by and summarizing the data.

It looks like either one or the combination of these two might help in getting your task done…

Let know if that doesnt help

Hi @sshitol
You can do that using VB code inside invoke code that will be more reliable

Thank You

hi @sshitol
follow the link below,

@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

hi, do u got any sample how to invoke the macro?

Hi! When use this code, there is a problem with compiling because this error appears
“Application is ambiguous in namespace Microsoft.Office.Interop.Exel”. Can you help me with this?

Heyy @Olivera_Kalinic

Check for the references in the bottom pane and remove the one which is erroring out and then add the same.