Automation error 'invalid worksheet cell'
This is somewhat long-winded, apologies, but I haven't been able to figure out why the error is happening.
As my automation is running, it records entries onto a spreadsheet. Effectively a log of each item that's processed. We are randomly encountering an 'invalid worksheet cell' error message (see attached screenshots). Frustratingly, this will happen apparently randomly, has been a different cell every time, and it doesn't happen on every automation run, etc. On checking the cell afterwards, it is always empty/null, it is not set to locked/hidden, and there are no macros on the file that might be affecting it. In fact, the file in question is a template we set up and, as I say, it doesn't happen on every run, so I know there are no issues with those particular cells of the file, or the file itself.
I could simply add 'try & catch' blocks for each interaction with Excel but this isn't really solving the root cause of the problem. The HRESULT suggests to retry later but are we talking seconds, minutes, or to exit Excel and come back in?
Interestingly, I have today noticed the error again but with a different exception error message. I wasn't able to get a screenshot, as the user dismissed it, but it was the age old 'object reference not set to an instance of an object' message. Now, I know for sure that something is being passed to the design block, especially since it manages to go through rows and rows of Excel doing the same thing before the error happens, sometimes hundreds like in the attached example.
This is somewhat long-winded, apologies, but I haven't been able to figure out why the error is happening.
As my automation is running, it records entries onto a spreadsheet. Effectively a log of each item that's processed. We are randomly encountering an 'invalid worksheet cell' error message (see attached screenshots). Frustratingly, this will happen apparently randomly, has been a different cell every time, and it doesn't happen on every automation run, etc. On checking the cell afterwards, it is always empty/null, it is not set to locked/hidden, and there are no macros on the file that might be affecting it. In fact, the file in question is a template we set up and, as I say, it doesn't happen on every run, so I know there are no issues with those particular cells of the file, or the file itself.
I could simply add 'try & catch' blocks for each interaction with Excel but this isn't really solving the root cause of the problem. The HRESULT suggests to retry later but are we talking seconds, minutes, or to exit Excel and come back in?
Interestingly, I have today noticed the error again but with a different exception error message. I wasn't able to get a screenshot, as the user dismissed it, but it was the age old 'object reference not set to an instance of an object' message. Now, I know for sure that something is being passed to the design block, especially since it manages to go through rows and rows of Excel doing the same thing before the error happens, sometimes hundreds like in the attached example.
I've tried to search for a solution to this on here but haven't found anything related. Unfortunately, searches on Google, MSDN, etc are contradicting one another, and some suggest that Office shouldn't really be automated, which I'm not convinced of.
My initial thoughts are that the runtime is trying to input data into Excel at the exact moment Excel is trying to do something else. Auto-saving, maybe?
***Moderator Edit-Vidyaranjan: Updated Platform Capability***
QBE European Operations
GB