Pivot Table Creation

[Onkar_Nath_Sharma]

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)

[GouthamVijay]

Hi @Onkar_Nath_Sharma

You can use Create Pivot Table Activity

[Onkar_Nath_Sharma]

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.

[GouthamVijay]

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

[mashy2]

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

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

[chandrashekar_padmin (chandrashekar padmini)]

Hello anyone?

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

[samir (Samir Mohite)]

Hello @chandrashekar_padmin, @Onkar_Nath_Sharma

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

Creating Pivot Table in the background

Hi @Michael_de_Borja I’ll suggest you to record macro in excel with creating Pivot table and use Invoke VBA activity to invoke that vba script. For that follow these steps :- Record macro with creating Pivot table, Save that Macro in a text file with a .txt extension. call the text file in invoke VBA code Activity. (and mention that method name in activity) place the invoke vba activity in a Excel Application Scope Activity. If any arguments need to pass to the method, pass in the “Entry Me…

[chandrashekar_padmin (chandrashekar padmini)]

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

[VAYILA_RAMESH]

Hi @chandrashekar_padmin,

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

[chandrashekar_padmin (chandrashekar padmini)]

Hello @VAYILA_RAMESH,

Yes issue was solved but used a code in uipath.

Thanks,
Padmini

[yvinothkumar (Vinoth Kumar Yuvaraj)]

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

[yvinothkumar (Vinoth Kumar Yuvaraj)]

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

[Mayur_Pawar]

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

[Mayur_Pawar]

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

[chandrashekar_padmin (chandrashekar padmini)]

Hi @Mayur_Pawar,

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

[Stalin_R]

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

[Nav29]

can you please tell how to make range dynamic