DrillDown Pivot Table

Help
1

Anybody know the workflow to create a drilldown pivot table sheet (sheet that shows data that is provided to the pivot table)? I cant really determine how to do it :confused:

2

Can you explain a bit more @csewall3?

1 Like
3

of course!

image
image1366×768 106 KB

double clicking on the C5 cell takes me to an alternate sheet (drills down) with order numbers. I was wondering how to replicate this in uipath

4

^^^^ see above :slight_smile:

5

I don’t think we can do that with the existing activities or even with the code :slight_smile: .
You can create same as the above pivot table by writing the code in VB. But adding the hyperlinks, need to check. You can search that.

Let me know if you want code to make the pivot table :slight_smile:

6

yes, that would be amazing. Would you be willing to walk me through how to implement it within the uipath environment?

7

additionally, what hyperlinks would I need to add in this instance?

8

here is the code to copy a two rows of data to a new sheet and create a pivot of that data.

You can enhance that according to your requirement . There are lot of documentation on all the below tags. Just go through them and enhance according to your requirement. Let me know you need any help

 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()
9

Clicking on C5 will redirect to some new sheet? That would be a hyperlink I guess.

Where it is navigating? Can you post a screenshot of where it navigates?

10

image
image1366×768 114 KB

it redirects to another worksheet within excel

11

^^ see above

12

if there is no external area (outside excel that the application goes to) do i still need to invoke vba?