When a large file (lets say a csv file greater than 50MB) is opened in Microsoft Excel, it opens with a little progress bar in the bottom right corner of the screen and it says "Press ESC to cancel". If you press the ESC key while the file is opening, it will cancel the operation. If a file is say 200MB, the time it takes Excel to open the file can be 20 seconds or more so it is good to have the ability to cancel.
In my VSTO addin, I would like to have the ability to cancel the file load operation for large workbooks. Since I am doing post processing on the file as well, I would like to have my own dialog box that shows progress while the file is opening/being processed (marquee progress bar while opening, and normal progress bar for post-processing). On this progress form, I would like to have a cancel button that can stop the current operation.
In order to display a custom marquee progress bar form while Excel is opening the file, the form must be created on a new thread since I believe Excel is single threaded so the form would freeze up if executed on the main thread.
My solution was to have this form execute on a separate thread and then have a Cancel button that uses SendKeys (or similar) to send an ESC keystroke when the Cancel button is pressed. However, this solution does not seem to work; using the various methods of sending keystrokes, either nothing happens or I get this exception "The message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER))"
The various methods that I have tried for sending keystrokes are:
//1
Globals.ThisAddIn.Application.SendKeys("{ESC}");
//also tried SendKeys.Send("{ESC}");
//2
Globals.ThisAddIn.Application.SendKeys("{ESC}", true);
Globals.ThisAddIn.Application.Wait(DateTime.Now + new TimeSpan(0, 0, 1));
//3 Nuget package to which uses SendInput from https://inputsimulator.codeplex.com/
InputSimulator inputSim = new InputSimulator();
inputSim.Keyboard.KeyPress(VirtualKeyCode.ESCAPE);
//4 sending keystorkes via Window hooking http://stackoverflow.com/questions/10407769/directly-sending-keystrokes-to-another-process-via-hooking
I also tried all of these methods using BeginInvoke without any success.
Does anyone have any suggestions for how I can get a Cancel button to successfully register an ESC keystroke with Excel? I'm guessing the solution has to do with handling the threading differently.
User contributions licensed under CC BY-SA 3.0