Create pivot table with dynamic data source range in uipath

Help
4

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