|
Brian Nesbitt
Guest
|
I am trying to do some WORK on an interval of 60 seconds. This is to run from addin startup complete until shutdown.
The WORK required can be defined as:
1) Read some data values from the current worksheet
2) Make a call to an external web service
3) Update a ADXRibbonButton icon value to notify the user of a change
First I tried using a typical winforms Timer but if the web call hangs (timeout or anything else happens etc) the Excel UI freezes. This obviously isn't ideal.
My second attempt used a System.Timers.Timer which uses a separate thread from the CLR. I had issues with Excel not closing properly sometimes. Rather than trying to fix it I figured another approach was better.
Most recently I have used a simple Thread (IsBackground = true) kicked off from the "AddinModule_AddinStartupComplete" method. This seems to work "most of the time". The Thread will "_shutdownEvent.WaitOne(60*1000)" and if that event is triggered it simply breaks from the "while(true)". If shutdown is not triggered it will perform the work. On "AddinModule_AddinBeginShutdown" the "_shutdownEvent" is set and then the thread is joined (with a timeout of 5 seconds) to hopefully wait on the background thread ending and gracefully shutdown the addin / Excel.
This generally seems to work but I have had a few exceptions occur, specifically on the excel access (ComException) and the ribbon button icon change "The message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER))".
I assume these exceptions are from accessing Excel and the UI from a thread other than the main UI thread. I also noticed that the ADXRibbonButton doesn't support "InvokeRequired" or "Invoke"'ing.
I think my current plan is to try going back to the winforms Timer and in the Tick event collect the necessary data required from Excel (since I am in the Main thread) then kick off (RunWorkerAsync) a BackgroundWorker to perform the web call and save the new required state of the ribbon button icon and exit. Then when that is completed it will fire the RunWorkerCompleted back in the Main thread again and I can update the ribbon button icon as required.
Any better methods? Thoughts? |
|
|
Andrei Smolin
Add-in Express team
Posts: 19213
Joined: 2006-05-11
|
Hello Brian,
Your assumption is correct: No code accessing the Excel object model (also any Office object model) can be run on a thread other than the main one. The object models are not thread-safe, this is the reason for RPC_E_SERVERCALL_RETRYLATER.
Consider using the SendMessage method and OnSendMessage event of the add-in module to create sort of a one-time timer. The OnSendMessage occurs on the main thread. Here's some code:
private const int WM_USER = 0x0400;
private const int MYMESSAGE = WM_USER + 1000;
//...
private void adxKeyboardShortcut1_Action(object sender)
{ this.SendMessage (MYMESSAGE); }
//...
private void AddinModule_OnSendMessage(object sender, AddinExpress.MSO.ADXSendMessageEventArgs e)
{
if (e.Message == MYMESSAGE)
{ //... }
}
That is, you send a custom Windows message to a hidden window that Add-in Express creates for your add-in. When the message gets delivered, the OnSendMessage event occurs. You filter out your message and do your stuff.
Andrei Smolin
Add-in Express Team Leader |
|
|
Brian Nesbitt
Guest
|
|
Thanks for confirming. I have some ideas and will post what I go with in a bit. |
|
|
Brian Nesbitt
Guest
|
I have a solution I am happy with. Some pseudo code:
AddIn
{
Initialize() { _checkService = new CheckService(); }
StartupComplete { _checkService.Start(); _winFormTimer.Elapsed = 60 * 1000; _winFormTimer.Start(); }
Tick { _checkService.TryWork(); // RUNS AS MAIN THREAD }
BeginShutdown {_winFormTimer.Stop(); _winFormTimer.Dispose(); _checkService.Stop(); }
ChangeIcon(newIcon) { _newIcon = newIcon; SendMessage(MYMESSAGE); }
OnSendMessage { if MYMESSAGE then ribbonButton.Icon = _newIcon;}
}
CheckService
{
Thread _checkThread;
_queue = new ThreadSafeQueue;
AutoResetEvent _workEvent
ManualResetEvent _shutdownEvent
Start() { _checkThread.Start(Run) }
TryWork()
{
// I run as the Main thread
// Access Excel to see if there is new work... if there is
AddWork(work)
}
AddWork() { _queue.Enqueue(work); _workEvent.Set(); }
Stop() {_shutdownEvent.Set(); _workEvent.Set(); _checkThread.Join(5000); }
Run()
{
while (true)
{
_workEvent.WaitOne(Timeout.Infinite);
if (_shutdownEvent.WaitOne(0)) { break; }
DoWork(_queue.Dequeue()); // this makes the call to AddIn.ChangeIcon(newIcon)
}
}
}
This all seems to work well. The only issue I have is that if Excel doesn't have focus when the AddIn.ChangeIcon() method is called (ie. the SendMessage is called) then the message is processed, but the ribbon (UI) isn't updated. As soon as I focus Excel (click on the window or ALT-TAB) the icon is updated immediately.
Any way of triggering the UI update to occur immediately even without focus? |
|
|
Andrei Smolin
Add-in Express team
Posts: 19213
Joined: 2006-05-11
|
Hello Brian,
Brian Nesbitt writes:
Any way of triggering the UI update to occur immediately even without focus?
No such way exists: Office doesn't trigger Ribbon callbacks for an inactive window. You'll see the same, if you open two Excel windows.
Andrei Smolin
Add-in Express Team Leader |
|
|
Brian Nesbitt
Guest
|
|
The response I expected! Thanks for confirming. |
|
Forums
Topics
Search
Help
Login
Register