Create Pivot table using UIPATH

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?

1 Like

@Raspi_Erwin here is an example:

2 Likes

@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

5 Likes

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

1 Like

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

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.

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

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

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

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

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

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!?

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.

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.

Excel file is opened in the vbscript close which is opened

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!?

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

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!

hey @sanjay21051990

I ran your sample code to create pivot table in my project.
I got the below error.

Invoke code : 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 3

error BC30002: Type ‘Microsoft.Office.Interop.Excel.ApplicationClass’ is not defined. At line 6
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 11

can you please help me here

1 Like

Restart your program.
It will work fine !!!