2

I am working with multi-threading in Excel and C# VSTO. When I write to an Excel sheet using the secondary thread and I, as the Excel user, interact with Excel while the writing is in progress, I get

Exception from HRESULT: 0x800AC472.

If I do not perform this interaction, the writing finishes successfully.

For this reason, I believe that if I could block or lock the user from interacting with Excel completely while the writing operation is in progress, I would not get this error message any longer. I have already attempted setting up sheet.EnableSelection = Microsoft.Office.Interop.Excel.XlEnableSelection.xlNoSelection to disable a user's ability to make selections, and then to enable it later once writing is done, however this does not block all interactions and the aforementioned error can still pop up. Yeah, multi-threading in Office applications is complicated and some people would go as far as to say that it is not recommended, but here we see that my technique is one which is suggested across the MSDN forums: Its not advisable to try to interact with the Excel object model in a multi-threaded way (except by using locks to force single-threaded access).

When performing an operation on VSTO without multithreading, Excel naturally locks up for the timebeing. I would like to trigger that behaviour when I would feel it is necessary in my program, as illustrated in this scenario.

Zeruno
  • 1,391
  • 2
  • 20
  • 39
  • Honestly I'm unfamiliar with using VS+C# but this nonetheless sounds like an [XY Problem](https://meta.stackexchange.com/a/66378/370758)... an issue with an attempted workaround to solve another problem. There's got to be a better way to prevent this error from simple user interaction... – ashleedawg Sep 03 '18 at 22:47
  • 1
    Take a look at this: https://social.msdn.microsoft.com/Forums/vstudio/en-US/9168f9f2-e5bc-4535-8d7d-4e374ab8ff09/hresult-800ac472-from-set-operations-in-excel , there some solid discussion on the matter. – ashleedawg Sep 03 '18 at 22:52
  • It's established that multithreading in Excel is complicated and there are only some workarounds which can work. In my case, this workaround is locking Excel from interaction by the user. And I am asking how to do just that. So I think it's straightforward what I am asking and certainly am not presenting an XY problem. – Zeruno Sep 03 '18 at 22:53
  • Thanks for the article. While I'm not sure whether to feel more confused after reading it, I think there are some ideas which I can apply to my project. However, my idea of temporarily blocking the user seems like a reasonable alternative which is not discussed there and of course does not answer the main question of this post. – Zeruno Sep 03 '18 at 23:01
  • Excel normally blocks when you don't use a separate thread, so the answer depends on why you're doing that in the first place. Showing a progress bar maybe? There might be some other way to do whatever it is. – Chris Sep 04 '18 at 13:48
  • I have a heavy piece of processing that I am offloading to the secondary thread and writing a result when it is done. I want the user to be able to continue using Excel while this happens. However, I want to temporarily disable user interaction with Excel while writing to Excel as an error is thrown when there is writing and user interaction at the same time, described in more detail in the previous comments. – Zeruno Sep 04 '18 at 15:01

1 Answers1

2

Your use case seems reasonable, so here's how I think you could get around it.

  1. You might be able to get away with this but I don't really know how solid it is. The asker of that question said it didn't work for him. I don't know why that would be; maybe he implemented it wrong, or maybe it has unstated limitations.

  2. Instead of making the change immediately, add a button to a task/actions pane, enable it when your calculations are done, and when it's clicked, modify the spreadsheet. This is a documented approach.

  3. Block the UI entirely, and probably add a progress bar. I do this frequently for things that take several seconds/minutes, it works fine as long as you're only accessing the API from one thread at a time. This is the progress bar I use (with some minor modifications).

Chris
  • 3,400
  • 1
  • 27
  • 41
  • I was the person asking the question in 1. No message filter was being trigerred despite implementation. Someone else pointed out that they had trouble, too, and that it does not handle all problems. 2 is a nice and practical idea, thanks a lot for that! Blocking the UI and interaction with Excel, and adding a progress bar while my addin writes to Excel is close to what I want - however I am not sure how to block the UI and interaction with Excel. I need to read your reccomended link in more detail. Thanks. – Zeruno Sep 05 '18 at 09:19
  • You just open a modal dialog, but it has to be there for the entirety of the operation, not just the last part. – Chris Sep 05 '18 at 12:43
  • As much as I like the solution in your link, I'm struggling to implement it. It seems implementing WPF with a VSTO project is not straightforward. Do you have some advice on this? – Zeruno Sep 06 '18 at 08:10
  • Are you talking about #2? You probably need [How to Use WPF Controls Inside an Excel Actions Pane?](https://stackoverflow.com/questions/24851562/how-to-use-wpf-controls-inside-an-excel-actions-pane) – Chris Sep 06 '18 at 13:51