How can I get a C# timer to execute on the same thread that created it?
Solution 1
The complete answer is as follows:
Problem: Class that writes data to excel unable to handle 'busy/reject' response messages from excel.
Solution: Implement IMessageFilter
interface as described here
IMessageFilter
Definition (from link):
namespace ExcelAddinMessageFilter
{
[StructLayout(LayoutKind.Sequential, Pack = 4)]
public struct INTERFACEINFO
{
[MarshalAs(UnmanagedType.IUnknown)]
public object punk;
public Guid iid;
public ushort wMethod;
}
[ComImport, ComConversionLoss, InterfaceType((short)1),
Guid("00000016-0000-0000-C000-000000000046")]
public interface IMessageFilter
{
[PreserveSig, MethodImpl(MethodImplOptions.InternalCall,
MethodCodeType = MethodCodeType.Runtime)]
int HandleInComingCall([In] uint dwCallType, [In] IntPtr htaskCaller,
[In] uint dwTickCount,
[In, MarshalAs(UnmanagedType.LPArray)] INTERFACEINFO[]
lpInterfaceInfo);
[PreserveSig, MethodImpl(MethodImplOptions.InternalCall,
MethodCodeType = MethodCodeType.Runtime)]
int RetryRejectedCall([In] IntPtr htaskCallee, [In] uint dwTickCount,
[In] uint dwRejectType);
[PreserveSig, MethodImpl(MethodImplOptions.InternalCall,
MethodCodeType = MethodCodeType.Runtime)]
int MessagePending([In] IntPtr htaskCallee, [In] uint dwTickCount,
[In] uint dwPendingType);
}
}
IMessageFilter
Implementation part of my class (see link):
#region IMessageFilter Members
int ExcelAddinMessageFilter.IMessageFilter.
HandleInComingCall(uint dwCallType, IntPtr htaskCaller, uint dwTickCount, ExcelAddinMessageFilter.INTERFACEINFO[] lpInterfaceInfo)
{
// We're the client, so we won't get HandleInComingCall calls.
return 1;
}
int ExcelAddinMessageFilter.IMessageFilter.
RetryRejectedCall(IntPtr htaskCallee, uint dwTickCount, uint dwRejectType)
{
// The client will get RetryRejectedCall calls when the main Excel
// thread is blocked. We can handle this by attempting to retry
// the operation. This will continue to fail so long as Excel is
// blocked.
// As an alternative to simply retrying, we could put up
// a dialog telling the user to close the other dialog (and the
// new one) in order to continue - or to tell us if they want to
// abandon this call
// Expected return values:
// -1: The call should be canceled. COM then returns RPC_E_CALL_REJECTED from the original method call.
// Value >= 0 and <100: The call is to be retried immediately.
// Value >= 100: COM will wait for this many milliseconds and then retry the call.
return 1;
}
int ExcelAddinMessageFilter.IMessageFilter.
MessagePending(IntPtr htaskCallee, uint dwTickCount, uint dwPendingType)
{
return 1;
}
#endregion
With the IMessageFilter
interface defined and implemented, I setup a STA Thread
and a Timers.Timer
as follows:
Thread:
thread = new Thread(WriteToExcel);
thread.SetApartmentState(ApartmentState.STA);
Timer:
timer = new System.Timers.Timer();
timer.Interval = 2000;
timer.Elapsed += new ElapsedEventHandler(StartSTAThread_Handler);
where StartSTAThread_Handler
is defined as:
void StartSTAThread_Handler(object source, ElapsedEventArgs e)
{
thread.Start();
thread.Join();
thread = null;
}
This thread calls the method I use to write to Excel and handles rejected messages with the IMessageFilter
interface described above. The last thing I had to do was to fully qualify excel OM references, that is; instead of:
Excel.Range rng = app.ActiveSheet.Range["range_name"];
rng.Copy(); // ERRROR: message filter's RetryRejectedCall is NOT called
I had to use fully qualified referencing:
app.ActiveSheet.Range["range_name"].Copy // OK: calls RetryRejectedCall when excel dialog etc is showing
While this seems to work for my needs, it does seem to contradict the "two dot rule" described by another poster here ...
Solution 2
You can use Timer.SynchronizingObject
property to marshal event-handler calls that are issued when an interval has elapsed.
Here's from MSDN:
When the Elapsed event is handled by a visual Windows Forms component, such as a button, accessing the component through the system-thread pool might result in an exception or just might not work. Avoid this effect by setting SynchronizingObject to a Windows Forms component, which causes the method that handles the Elapsed event to be called on the same thread that the component was created on.
Assuming you are using a WinFrom and you are creating the timer instance from within the main form:
System.Timers.Timer t = new System.Timers.Timer();
t.SynchronizingObject = this;
t.Elapsed += t_Elapsed;
t.Start();
Pat Mustard
Updated on June 13, 2022Comments
-
Pat Mustard almost 2 years
I want to enable
IMessageFilter
on an Excel Addin I have to write to excel. I have taken example from here which says:Message filters are per-thread, so we register this thread as a message filter (not the main thread that the add-in is created on - because that's Excel's main thread
My problem is that my system writes to excel when a timer elapses which results in the writing method being called from a ThreadPool thread which breaks
IMessageFilter
since the excel cannot access theRetryRejectedCall
part ofIMessageFilter
, because it lives on the caller thread rather than the executing one spawned by the timer.So, my question is: is there a way that I can force a timer's Elapsed event to run on the same thread that initialized the timer?
EDIT:
My Question is, how do I make
IMessageFilter
catch excel errors when it throws reject/busy?Thx