Writing excel with pivot table

excel
uiautomation
activities

#1

I am trying to write a range on an excel file that already has a pivot on a different tab. But for some reason i am getting this error:

Source: Write cell

Message: Object reference not set to an instance of an object. This error usually occurs when using a variable with no set value (not initialized).

Exception Type: NullReferenceException

System.NullReferenceException: Object reference not set to an instance of an object.
at ClosedXML.Excel.XLWorkbook.LoadSpreadsheetDocument(SpreadsheetDocument dSpreadsheet) in C:\Git\ClosedXML\ClosedXML\Excel\XLWorkbook_Load.cs:line 433
at ClosedXML.Excel.XLWorkbook.LoadSheets(String fileName) in C:\Git\ClosedXML\ClosedXML\Excel\XLWorkbook_Load.cs:line 45
at ClosedXML.Excel.XLWorkbook.Load(String file) in C:\Git\ClosedXML\ClosedXML\Excel\XLWorkbook_Load.cs:line 34
at ClosedXML.Excel.XLWorkbook…ctor(String file, XLEventTracking eventTracking) in C:\Git\ClosedXML\ClosedXML\Excel\XLWorkbook.cs:line 710
at ClosedXML.Excel.XLWorkbook…ctor(String file) in C:\Git\ClosedXML\ClosedXML\Excel\XLWorkbook.cs:line 699
at UiPath.Excel.WorkbookFile…ctor(String workbookPath, String password, Boolean createNew)
at UiPath.Excel.Activities.WorkbookActivity`1.BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, Object state)
at System.Activities.AsyncCodeActivity.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)

Could you please advice me whether is possible to work with such type of excel or not?
I really need the pivot table to stay in the excel when i insert the new data.

Thank you!


#2

Hi @Lucia_Soriano

This error usually occurs when you have not set the value of a variable or argument. Try using a log message activity with [VariableYouAreWrtiingIntoCell].tostring (this will work with a number, you will need a different condition for other variable types) before the write cell activity, run the automation and if you see that it has left a blank log in the output pane, you know that that variable/argument is empty which is causing you error. if this is the case you will need to trace that variable/argument back to find the problem, which will be something to do with assigning the value of the variable/argument such as no assign, or the argument isn’t linked or something similar


#3

Hi

I have also experienced similar issue in past with write cell activity so I found some work around of it that is I am using Invoke code Activity rather than using write cell activity where I am creating the object of the excel using the dot code inside this activity to manipulate excel.
I use this activity to create the pivot table also on run time. As this worked for me so I think it will help you a lot. !!

Example of code as below:
wb.PivotTableWizard(XlPivotTableSourceType.xlDatabase ,wsTemp.Range(“A1:C” & wsTemp.UsedRange.Rows.Count),ws.Range(“A1”),“Pivot1”,True,True,True,True,False,False,XlOrder.xlDownThenOver ,0)
pt = CType(ws.PivotTables(“Pivot1”), Microsoft.Office.Interop.Excel.PivotTable)
field1 = CType(pt.PivotFields(“Sold To”), Microsoft.Office.Interop.Excel.PivotField)
field2 = CType(pt.PivotFields(“Amount”), Microsoft.Office.Interop.Excel.PivotField)
field1.Orientation = XlPivotFieldOrientation.xlRowField
field2.Orientation = XlPivotFieldOrientation.xlDataField
field2.Function = XlConsolidationFunction.xlSum

Note: Invoke code Activity accept VB.Net code only


#4

I think it is a nice approach and it worked for me.
Good Job. Cheers.

I too have a alternate for this issue…
We can use Excel as an database using oleDB connection. It will be very efficient and fast.

While using this, you have to do is take care about the text formats which we want to manipulating with Excel.

try this hopefully it will work for you guys.


#5

Hi Deepesh,

I tried same code but having some issue during execution , using environment window 7 & Microsoft office version 2007 .
Is it possible that as I am using older version of the excel so this may be a reason .

wb.PivotTableWizard(XlPivotTableSourceType.xlDatabase ,wsTemp.Range(“A1:C” & wsTemp.UsedRange.Rows.Count),ws.Range(“A1”),“Pivot1”,True,True,True,True,False,False,XlOrder.xlDownThenOver ,0)
pt = CType(ws.PivotTables(“Pivot1”), Microsoft.Office.Interop.Excel.PivotTable)
field1 = CType(pt.PivotFields(“Sold To”), Microsoft.Office.Interop.Excel.PivotField)
field2 = CType(pt.PivotFields(“Amount”), Microsoft.Office.Interop.Excel.PivotField)
field1.Orientation = XlPivotFieldOrientation.xlRowField
field2.Orientation = XlPivotFieldOrientation.xlDataField
field2.Function = XlConsolidationFunction.xlSum

Could you please share your environment office version . ?


#6

Hi Paritosh,

I am using this code on version 10 of windows and Microsoft office 2010, I didn’t try on 2007 may be this is the version issue.


#7

Guys ,
Its working fine for me as expected.

wb.PivotTableWizard(XlPivotTableSourceType.xlDatabase ,wsTemp.Range(“A1:C” & wsTemp.UsedRange.Rows.Count),ws.Range(“A1”),“Pivot1”,True,True,True,True,False,False,XlOrder.xlDownThenOver ,0)
pt = CType(ws.PivotTables(“Pivot1”), Microsoft.Office.Interop.Excel.PivotTable)
field1 = CType(pt.PivotFields(“Sold To”), Microsoft.Office.Interop.Excel.PivotField)
field2 = CType(pt.PivotFields(“Amount”), Microsoft.Office.Interop.Excel.PivotField)
field1.Orientation = XlPivotFieldOrientation.xlRowField
field2.Orientation = XlPivotFieldOrientation.xlDataField
field2.Function = XlConsolidationFunction.xlSum


#8

Hi Paritosh,

Could you please share the Pivot XAML file for creation of Pivot using excel.


#9

If you share today itself then it would be very helpful for me.