Create Pivot table using UIPATH

Help
,
21

Hi @sob,

I restarted,but no luck:(

22

hi,
Sound like you are missing the nampace Microsoft.Office.Interop.Excel please import it in your file :slight_smile:

23

yeah ,i did that aleady

25

Could you share your file

26

I too faced the same issue but after restarting my program ,the error gone…it is working fine

27

Check this .

image
image815×166 6.99 KB

Have you import this ?

28

Updated Version
includes:-
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’
System.Windows.MessageBox.Show(s.Name)
''s.Visible=False ‘inSelect the check box’
Next

4.Enable multi Select

oPivotField.EnableMultiplePageItems=True ‘enable of mulitselect’

5.Selection of single item

oPivotField.CurrentPage=“Basics” ‘Select the Basic in filter’
oPivotField.CurrentPage=“Toys”

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’’
Try
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’
excel.Visible=True

ws=CType(wb.Sheets(“Summary”),Microsoft.Office.Interop.Excel.Worksheet)‘select a sheet and activiates’
ws.Activate

'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

oPivotTable=DirectCast(ws.PivotTables(“Summary_1”),Microsoft.Office.Interop.Excel.PivotTable)

oPivotTable.TableRange2.Copy

wb = excel.Workbooks.Open(“D:\Users\Default User\Documents\UiPath\copy of pivot tables\pivots.xlsx”)‘Open the excel the file’
excel.Visible=True

p_ws=CType(wb.Sheets(“sheet1”),Microsoft.Office.Interop.Excel.Worksheet)‘select a sheet and activiates’
p_ws.Activate
''p_ws.Cells.PasteSpecial(XlPasteType.xlPasteValuesAndNumberFormats)
p_ws.Range(“A15”).PasteSpecial(XlPasteType.xlPasteValuesAndNumberFormats)

‘Summary_1’
Catch es As Exception
System.Windows.MessageBox.Show(es.Message)
End Try

Regards
Sanjay Shankhla

2 Likes
29

Hi,

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?
image pivot_table.zip (27.5 KB)

30

Hi @kavyashreeh22,

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
image

Plz Correct it and run again

Regards
Sanjay

31

Hi Sanjay,
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.

32

Hi @kavyashreeh22

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[1], 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 Save() method:

xlWorkBook.Save();

And yes you can pass an argument in the invok e code editor

Regards
Sanjay shankhla

33

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,

image

34

Hi @kavyashreeh22,

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

Regards
Sanjay Shankhla :slight_smile:

35

Attached sample pivot table in Sheet1.sample test data.xlsx (21.5 KB)

36

hi @kavyashreeh22,

please check you can read the pivot using read range

pivot_readfile.zip (20.4 KB)

regards
Sanjay

1 Like
37

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.

Regards,
Karthikeyan R

2 Likes
38

Hi Sanjay,
We are facing “writing lock file to disk. path=…alldependencies.json” error while doing it in new project.

39

hi, this is very useful
but how to select excel sheet with dynamic range?

40

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.

image

wb.SaveAs(“C:\test.xlsx”)
wb.Close(savechanges:=True)

41

Hello @sanjay21051990
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)