Create pivot table with dynamic data source range in uipath

Hi,

I want to create pivot table in excel using uipath with many filtration.
I have seen many examples using vb but all of it hardcoded the data source range

For an example:
image

Is there any way to specify dynamic range using vb code or ways to create pivot table using uipath build in activities?

Thanks

@mashy2,

You can get the range once you read the worksheet. For example, if your worksheet is
“WS”, then the range you need is ws.usedrange.Rows.Count… Then you can specify the range as

sheetRange = ws.usedrange.rows.count

rng = ws.range(“B1”, “G” + sheetRange.tostring)

Or you can just give the cells whatever you want as

rng = CType(newSheet.Cells(2, 7), Microsoft.Office.Interop.Excel.Range)

2 represents “B” column and 7 represents “G” here.

Let me know if you want sample code

1 Like

Hi, Can give me sample code?

Thanks so much!!

Here it is @mashy2,

May be it is bit clumpsy, try to understand :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 'create the instance of excel work book'
    Dim p_ws As Microsoft.Office.Interop.Excel.Worksheet
    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 num As Object

    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 s1 As Microsoft.Office.Interop.Excel.PivotItems = Nothing
    '****************************************************************************************'
    
    wb = excel.Workbooks.Open(Path, [ReadOnly]:=False) '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 MTUsedRange As Integer = ws.UsedRange.Rows.Count

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

    rng = newSheet.UsedRange
    p_rng = CType(newSheet.Cells(1, 7), 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'


    oPivotField = CType(oPivotTable.PivotFields("Product"), 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
    s1 = CType(oPivotField.PivotItems, Microsoft.Office.Interop.Excel.PivotItems)


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

    oPivotField = CType(oPivotTable.PivotFields("Foreign Amount"), Microsoft.Office.Interop.Excel.PivotField) 'Specfiy the coulmn name'
    oPivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
    oPivotField.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum
    oPivotField.Name = "Sum of Foreign Amount"
    'oPivotField.DragToColumn = True

    CType(oPivotTable.PivotFields("Sum of Foreign Amount"), Microsoft.Office.Interop.Excel.PivotField).Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField

    oPivotField = CType(oPivotTable.PivotFields("Foreign Amount"), 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.Name = "Count of Foreign Amount"
	
	 wb.Save()
    wb.Close()
    excel.Quit()
1 Like

Thanks so much!

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.