Create Pivot table using UIPATH

excel
activities

#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 .

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


#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

https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.tools.excel.worksheet.names?view=vsto-2017

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