I recently discovered the following behavior. I want to post here to see if anyone has seen this behavior before and get ideas on how to solve my current problem.
When using the Read Range and Read Cell activities under App Integration (the ones that require an Excel Application Scope), cells with the “#NAME?” error are read as the integer (System.Int32) 0.
When using the Read Range and Read Cell activities under System > File > Workbook (the ones that don’t require an Excel Application Scope), cells with the “#NAME?” error are read as the integer (System.Int32) -2146826259. This is not the same value (although it is fairly close) to Int32.MinValue.
IMHO, the behaviors of these two activities should a) be synced and b) not return a number when reading “#NAME?” as this can be misleading and cause unnecessary technical limitations (my current limitation is described below).
My current problem:
I need to read cells from a spreadsheet that may contain the “#NAME?” error or some number. If the cell is “#NAME?” I need to ignore that cell. If the cell is a number, I need to add it to a report. Modifying the report format would be quite difficult, so I don’t want to go that route. Since the “#NAME?” error always returns a number when read, I have no way of differentiating between the “#NAME?” cells and the cells I need to report on.
The only workaround that seems feasible is to use the App Integration activities and treat -2146826259 as “#NAME?”. However, I don’t like this solution because of the small chance that one day I actually miss a valid cell with -2146826259. I know it’s a very remote possibility, but if there is another solution I’d like to avoid this risk altogether, so I am posting here in the hope that someone has a better answer. Please let me know.