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.
Behavior:
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.
Thank you,
Michael