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?
Whilst the try & catch blocks will help me to suppress the error, they don't solve the root cause of the issue. As this prevents me writing to cells, I have to consider how to continue outputting to the sheet without affecting it...
And asking the business to turn off auto-save in Excel for the sake of this automation is not viable, nor should it be necessary. Especially since I don't know for certain if that is the root cause...
I am currently investigating using a lookup table and writing to memory until the full process is complete, then output the whole table to excel at the end in one go, re-attempting where necessary but perhaps others may have a better idea?