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