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