Pivot Table Creation

Can somebody please help me to get the pivot table workflow created for the attached file.
Input is sheet1 and output should be like sheet2

File.XLSX (89.3 KB)

Hi @Onkar_Nath_Sharma

You can use Create Pivot Table Activity

i can use but how…that’s my query. If you can attach the screenshot of the activity or attach an XAML file, it would be better.

@Onkar_Nath_Sharma

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(“C:\Users\sushmi\Desktop\New Microsoft Office Excel Worksheet (2).xlsx”)‘Open the excel the file’
excel.Visible=True

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

rng=ws.Range(“A8”,“F510”)‘Selects the given range of sheet 1’

p_ws=CType(wb.Sheets.Add,Microsoft.Office.Interop.Excel.Worksheet)‘create a empty and and name the sheet as pivot table’

p_rng= CType(p_ws.Cells(1,1),Microsoft.Office.Interop.Excel.Range) ‘Specify the first cell for the pivot table’

'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

oPivotCache=CType(wb.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase,rng),Microsoft.Office.Interop.Excel.PivotCache)
oPivotTables=DirectCast(p_ws.PivotTables(Type.Missing),Microsoft.Office.Interop.Excel.PivotTables)
oPivotTable=oPivotTables.Add(oPivotCache,p_rng,“Summary”,Type.Missing,Type.Missing)‘first paramenter is cache,range the table should start,table name’

‘Creation of pivot Fields’

Dim oPivotField As Microsoft.Office.Interop.Excel.PivotField=Nothing

oPivotField=CType(oPivotTable.PivotFields(“Product Category”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField

oPivotField=CType(oPivotTable.PivotFields(“Product Name”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField

oPivotField=CType(oPivotTable.PivotFields(“Country”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField

oPivotField=CType(oPivotTable.PivotFields(“Revenue”),Microsoft.Office.Interop.Excel.PivotField)‘Specfiy the coulmn name’
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
oPivotField.Function=Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum
oPivotField.Name=“Sum_Of_revenue”

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

You can use the above code using Invoke Code Activity and
make sure you change the path name, file name and column headers

Thanks.

HI,
Do you have any idea how to make range dynamic?

rng=ws.Range(“A8”,“F510”)‘Selects the given range of sheet 1’

Hello anyone?

Can you pls answer above question i also have same query?

Hello @chandrashekar_padmin, @Onkar_Nath_Sharma

Guys, just go through below thread, this help you. :slight_smile:

Hello @samir,

I have tried it , but still the same issue it is taking only particular , if data is dynamic it is considering the same.

Please let me know

Hi @chandrashekar_padmin,

have you resolved your concern? if yes, please share inputs because i am also having same concern.

Hello @VAYILA_RAMESH,

Yes issue was solved but used a code in uipath.

Thanks,
Padmini

Hi Team

I have used the above VB Code to create a pivot table but I was facing getting an error while running the code. Please refer below error message.

Source: Excel Pivot Table

Message: Exception has been thrown by the target of an invocation.

Exception Type: System.Reflection.TargetInvocationException

An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is:
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ----> System.Runtime.InteropServices.COMException: Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32 aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.PivotCaches.Add(XlPivotTableSourceType SourceType, Object SourceData)
at UiPathCodeRunner_727b97feb3cd489ea00892bdcb8ad11a.Run(String ECDExcelFilePath, String SourceRange)
— End of inner ExceptionDetail stack trace —
at UiPath.Executor.WorkflowRunner.EndExecute(IAsyncResult result) in D:\a\1\s\Robot\UiPath.Executor\WorkflowRuntime.cs:line 181
at UiPath.Core.Activities.InvokeWorkflowFile.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)

Currently I am using studio version 2018.3.3.

Anyone could you please help me on this.

Thanks

Hi Team

I am able to resolve the issue by passing range as string instead of object at line
oPivotCache=CType(wb.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase,rng),Microsoft.Office.Interop.Excel.PivotCache)

Thanks

can you share that code …if you don’t mind @chandrashekar_padmin

have you got the answer of how to make it dynamic ? @mashy2

Hi @Mayur_Pawar,

Please find xaml attached , please change the column name accordingly.
Range is dynamic here.
Example.xaml (9.0 KB)

2 Likes

How to mark all the empty cell to zero(or desired value) in Pivot table in above code that you provided