Create Pivot table using UIPATH

excel
activities

#1

Hi I am planning to make a pivot table using UIpath.
Is there a way to make a pivot using UIpath? anyone have experienced on this? what function to use?


Reprocess Excel to another Table Format
#2

@Raspi_Erwin here is an example:


#3

@Raspi_Erwin,

Try this approach without any short-cuts you can achieve this using vb.net code
find the attach example…
pivot_table.zip (10.9 KB)

note:this would run from 2017.1.6522 version of ui-path
:slight_smile:

regards
sanjay shankhla


How to make pivot table in an excel spreadsheet uipath
PivotTable error
PivotTable error
#4

Hai @sanjay21051990, Can you tell me once how to write the VB.net code to build the pivot table!?


#5

@sushmithaelluru i have attached sample above :slight_smile:
regards
sanjay shanhkla


#6

Thankyou @sanjay21051990.

I got the following error, can you tell me the reason for the error.

The xaml file is attached below,
PivotTable.xaml (13.1 KB)

PivotTable has thrown an exception

Source: Invoke code

Message: Error compiling code
error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Application’. Are you missing an assembly reference? At line 1
error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Workbook’. Are you missing an assembly reference? At line 2
error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Worksheet’. Are you missing an assembly reference? At line 4
error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Worksheet’. Are you missing an assembly reference? At line 5
error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Range’. Are you missing an assembly reference? At line 6
error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Range’. Are you missing an assembly reference? At line 7
error BC30002: Type ‘Microsoft.Office.Interop.Excel.ApplicationClass’ is not defined. At line 9
error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Worksheet’. Are you missing an assembly reference? At line 14
error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Worksheet’. Are you missing an assembly reference? At line 19
error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Range’. Are you missing an assembly reference? At line 21
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotCache’ is not defined. At line 24
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotTables’ is not defined. At line 25
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotTable’ is not defined. At line 26
error BC30456: ‘XlPivotTableSourceType’ is not a member of ‘Excel’. At line 28
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotCache’ is not defined. At line 28
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotTables’ is not defined. At line 29
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotField’ is not defined. At line 34
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotField’ is not defined. At line 37
error BC30456: ‘XlPivotFieldOrientation’ is not a member of ‘Excel’. At line 38
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotField’ is not defined. At line 41
error BC30456: ‘XlPivotFieldOrientation’ is not a member of ‘Excel’. At line 42
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotField’ is not defined. At line 44
error BC30456: ‘XlPivotFieldOrientation’ is not a member of ‘Excel’. At line 45
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotField’ is not defined. At line 47
error BC30456: ‘XlPivotFieldOrientation’ is not a member of ‘Excel’. At line 48
error BC30456: ‘XlConsolidationFunction’ is not a member of ‘Excel’. At line 49

Exception Type: ArgumentException

System.ArgumentException: Error compiling code
error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Application’. Are you missing an assembly reference? At line 1
error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Workbook’. Are you missing an assembly reference? At line 2
error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Worksheet’. Are you missing an assembly reference? At line 4
error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Worksheet’. Are you missing an assembly reference? At line 5
error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Range’. Are you missing an assembly reference? At line 6
error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Range’. Are you missing an assembly reference? At line 7
error BC30002: Type ‘Microsoft.Office.Interop.Excel.ApplicationClass’ is not defined. At line 9
error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Worksheet’. Are you missing an assembly reference? At line 14
error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Worksheet’. Are you missing an assembly reference? At line 19
error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Range’. Are you missing an assembly reference? At line 21
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotCache’ is not defined. At line 24
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotTables’ is not defined. At line 25
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotTable’ is not defined. At line 26
error BC30456: ‘XlPivotTableSourceType’ is not a member of ‘Excel’. At line 28
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotCache’ is not defined. At line 28
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotTables’ is not defined. At line 29
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotField’ is not defined. At line 34
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotField’ is not defined. At line 37
error BC30456: ‘XlPivotFieldOrientation’ is not a member of ‘Excel’. At line 38
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotField’ is not defined. At line 41
error BC30456: ‘XlPivotFieldOrientation’ is not a member of ‘Excel’. At line 42
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotField’ is not defined. At line 44
error BC30456: ‘XlPivotFieldOrientation’ is not a member of ‘Excel’. At line 45
error BC30002: Type ‘Microsoft.Office.Interop.Excel.PivotField’ is not defined. At line 47
error BC30456: ‘XlPivotFieldOrientation’ is not a member of ‘Excel’. At line 48
error BC30456: ‘XlConsolidationFunction’ is not a member of ‘Excel’. At line 49

at UiPath.Core.Activities.Workflow.CompilerRunner.Compile(String code, Int32 errLineOffset, Boolean generateInMemory)
at UiPath.Core.Activities.Workflow.CompilerRunner…ctor(String code, String className, String methodName, Int32 errLineOffset, Boolean generateInMemory)
at UiPath.Core.Activities.InvokeCode.GetCompilerRunner(String userCode, List`1 args, String imps)
at UiPath.Core.Activities.InvokeCode.Execute(CodeActivityContext context)
at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.ActivityInstance.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

Regards,
Sushmitha.


#7

please include the namespace
called Microsoft.Office.Interop.Excel in xaml file
:slight_smile:


#8

Can I know where to include the namespace @sanjay21051990:neutral_face:


#9

@sushmithaelluru click import and type Microsoft.Office.Interop.Excel follow below link image to import


#10

Even after I add the namespace also, am getting the same error, @indra @sanjay21051990.


#11

@sushmithaelluru
check this
:slight_smile:
PivotTable.xaml (13.7 KB)


#12

Ya got it @sanjay21051990, Thanks much.
And can i know how to write the vb coding, I mean how to learn and implement the vb code!?


#13

Hai @indra, am getting the below error attached,

But in my requirement I need to create that excel sheet with pivot table and send an email.


#14

Hai @sanjay21051990, it is showing the child warnings for close workbook activity, even if I declare the scope of the workbook variable globally!!

I attached the xaml file, can you please check it once.
PivotTable.xaml (18.1 KB)

Regards,
Sushmitha.


#15

Excel file is opened in the vbscript close which is opened


#16

wb = excel.Workbooks.Open(“C:\Users\sushmi\Desktop\New Microsoft Office Excel Worksheet (2).xlsx”)‘Open the excel the file’
excel.Visible=True

The above expression is one where we open the excel sheet!? @indra!?


#17

At the end of the program use this excel.quit in vbcode


#18

But also not able to send email @indra, even I use excel.quit, getting the error as the excel is being used by another process!