yeah ,i did that aleady
Could you share your file
I too faced the same issue but after restarting my program ,the error gone…it is working fine
1. Filtration of pivots and select and unselect
2. Hiding of Subtotal
3.Selection the pivot item
Dim s1 As Microsoft.Office.Interop.Excel.PivotItems=CType(oPivotField.PivotItems,Microsoft.Office.Interop.Excel.PivotItems)
System.Windows.MessageBox.Show(CType(s1(0),Microsoft.Office.Interop.Excel.PivotItem).Name)‘Returns forst value of the filed 0 is the index’
For Each s As Microsoft.Office.Interop.Excel.PivotItem In CType(oPivotField.PivotItems,Microsoft.Office.Interop.Excel.PivotItems)‘Returns the feild items’
''s.Visible=False ‘inSelect the check box’
4.Enable multi Select
oPivotField.EnableMultiplePageItems=True ‘enable of mulitselect’
5.Selection of single item
oPivotField.CurrentPage=“Basics” ‘Select the Basic in filter’
pivot_table.zip (11.4 KB)
Copying of pivot table below the code
Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim ws As Microsoft.Office.Interop.Excel.Worksheet
Dim p_ws As Microsoft.Office.Interop.Excel.Worksheet
Dim rng As Microsoft.Office.Interop.Excel.Range ‘capturing the range of sheet1 ‘’’
Dim p_rng As Microsoft.Office.Interop.Excel.Range ‘capturing the range of pivottable shhet1’’
excel = New Microsoft.Office.Interop.Excel.ApplicationClass’create the instance of excel work book’
wb = excel.Workbooks.Open(“D:\Users\Default User\Documents\UiPath\copy of pivot tables\01-Stock at RNA.xlsx”)‘Open the excel the file’
ws=CType(wb.Sheets(“Summary”),Microsoft.Office.Interop.Excel.Worksheet)‘select a sheet and activiates’
'Creation pivot Cache and pivot table ’
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
wb = excel.Workbooks.Open(“D:\Users\Default User\Documents\UiPath\copy of pivot tables\pivots.xlsx”)‘Open the excel the file’
p_ws=CType(wb.Sheets(“sheet1”),Microsoft.Office.Interop.Excel.Worksheet)‘select a sheet and activiates’
Catch es As Exception
I used the code which you have shared and replaced with the fields from my excel file. But I am getting the error pop “The pivot table field name is not valid”. Can you pleas help?
pivot_table.zip (27.5 KB)
Found the mistake
in sample file the headers starts at A1
and you have given from a2,x20 this is why you getting the error as field names are invalid
Plz Correct it and run again
Thanks so much, its working fine now.
Can you please tell me how we can assign a sheet name to the sheet where the pivot table is generated. It assigns as “Sheet*” by default.
Also, how can we pass the arguments to the code? Like filepath, sheet name etc.
To add a new worksheet to the workbook use this code:
var xlSheets = xlWorkBook.Sheets as Excel.Sheets; var xlNewSheet = (Excel.Worksheet)xlSheets.Add(xlSheets, Type.Missing, Type.Missing, Type.Missing); xlNewSheet.Name = "newsheet"; // Uncomment a line below if you want the inserted sheet to be the last one //xlWorkBook.Sheets.Move(After: xlWorkBook.Sheets.Count);
To save the workbook call
And yes you can pass an argument in the invok e code editor
Thanks, but my question is, this Pivot table will be generated in a separate new sheet and will be named Sheet1, Sheet2 etc. Is there any way we can specify the sheet name while generating the Pivot table?
Also, how can we read this pivot table using UiPath? Using excel read range, I am getting below error,
Naming of the sheet is possible using the worksheet.Name property for more details i have provided the details please check in the below link
coming to Read pivot Using i believe yes we can.just for the demo can attach some pivot file
Attached sample pivot table in Sheet1.sample test data.xlsx (21.5 KB)
please check you can read the pivot using read range
pivot_readfile.zip (20.4 KB)
Hi Cheersrpa, @cheersrpa
Please add a new variable to the Workflow of the type (Microsoft.Office.Interop.Excel).
Save the file and run, it will work fine.
We are facing “writing lock file to disk. path=…alldependencies.json” error while doing it in new project.
hi, this is very useful
but how to select excel sheet with dynamic range?
Sanjay, thanks for the pivot xml vbcode, works very well. Though after the table is created, the workbook is read only and is not saved. I tried to modify with the code below to save and quit the workbook, but no luck. Please advise. thanks.
I can see that you have written Vb code , but can u pls help me out in reading range from excel which is dynamic (as my data keeps changing every week) also can you help me to write code for filters in pivot so that i get data only for particular filter.
Please find my sample sheet .Sample.xlsx (1.4 MB)
Could you also explain on how to add multiple data columns, right now you have only one data field and I wanted to enter multiple fields with sum. Any help is appreciated. @sanjay21051990
Hi Sanjay, That code was perfect. But can you help me with the code for Filtering the fields in the created pivot table. I very much need it to achieve the deadline.