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:
Is there any way to specify dynamic range using vb code or ways to create pivot table using uipath build in activities?
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
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()