I have a user requirement that I have been battling with for a while with no success. I need to write an add-in that can read around 100 formula-driven cells (of a specific spreadsheet) once every couple of minutes, and send to a web service.
I'm more than happy to use Excel-DNA or VSTO, but everything I've tried so far causes the user interface to hang for an instant. Would this always be the case if the data is being read from the active spreadsheet (even from a different thread) ?
Reading the sheet from a different thread is likely to have a worse effect than reading from the Excel main thread (say in an event or something). This is due to the COM threading switch that is required for the cross-thread calls. In the end, all the COM calls have to do their work on the main thread anyway.
You might have more success by hooking some of the Excel events, as a start the Workbook.SheetChange event, then checking whether the changes affect your watched Range(s) and updating an internal data structure with the new data.
You can then update the back-end periodically (or only when watched cells have change) from a background thread.