VBA macro not executing when called from a separate thread

[Omatase]

I am making the following call from a C# app to a VBA macro in Word:

_wordApp.GetType().InvokeMember("Run",
System.Reflection.BindingFlags.Default |
System.Reflection.BindingFlags.InvokeMethod, null, _wordApp,
argumentList);

This call is successful when made from the main thread of my
application. When I make the same call from another, newly spawned,
thread nothing happens. The call to the macro never returns and no
line in the macro is ever processed.

I'm sure this is a common problem for folks dealing with mutithreading
and vba macro calls but I didn't find anything in about it online. Is
there something I need to do differently to get this to work properly?

 

[Nicholas Paldino [.NET/C# MVP]]

Omatase,

This is happening because you do not have the COM apartment state set up
on the thread. On the thread, before you make any calls to COM components,
you will want to do this:

Thread.CurrentThread.SetApartmentState(ApartmentState.STA);

It will set up the your other thread to be a single-threaded apartment.
Your call should work then.

 

[Willy Denoyette [MVP]]

I am making the following call from a C# app to a VBA macro in Word:

_wordApp.GetType().InvokeMember("Run",
System.Reflection.BindingFlags.Default |
System.Reflection.BindingFlags.InvokeMethod, null, _wordApp,
argumentList);

This call is successful when made from the main thread of my
application. When I make the same call from another, newly spawned,
thread nothing happens. The call to the macro never returns and no
line in the macro is ever processed.

I'm sure this is a common problem for folks dealing with mutithreading
and vba macro calls but I didn't find anything in about it online. Is
there something I need to do differently to get this to work properly?

This should work provided you call the "Run" method from the _wordApp
creating thread.

Following should work...
...
Thread t = new Thread(delegate()
{
object _wordApp = Marshal.GetActiveObject("Word.Application"); //
connect to the running word instance through a proxy
_wordApp.GetType().InvokeMember("Run",System.Reflection.BindingFlags.Default
|
System.Reflection.BindingFlags.InvokeMethod, null, _wordApp, new
object[] {"SomeMacro"});
});

t.IsBackground = true;
t.Start();

....

Willy.

 

[Willy Denoyette [MVP]]

I am making the following call from a C# app to a VBA macro in Word:

_wordApp.GetType().InvokeMember("Run",
System.Reflection.BindingFlags.Default |
System.Reflection.BindingFlags.InvokeMethod, null, _wordApp,
argumentList);

This call is successful when made from the main thread of my
application. When I make the same call from another, newly spawned,
thread nothing happens. The call to the macro never returns and no
line in the macro is ever processed.

I'm sure this is a common problem for folks dealing with mutithreading
and vba macro calls but I didn't find anything in about it online. Is
there something I need to do differently to get this to work properly?

This should work provided you call the "Run" method from the _wordApp
creating thread.

Following should work...
..
Thread t = new Thread(delegate()
{
object _wordApp = Marshal.GetActiveObject("Word.Application"); //
connect to the running word instance through a proxy

_wordApp.GetType().InvokeMember("Run",System.Reflection.BindingFlags.Default
|
System.Reflection.BindingFlags.InvokeMethod, null, _wordApp, new
object[] {"SomeMacro"});
});

t.IsBackground = true;
t.Start();

...

Willy.

After further thinking, I'm pretty sure this should even work when the
object was created on another thread. What makes you believe this doesn't
work, you aren't touching the UI from the newly created thread do you? Mind
to share some code that illustrates the issue?

Willy.

 

[Omatase]

Thanks for the info so far, here is all of the relevant code: I added
the line "batchEmails.SetApartmentState(ApartmentState.STA);"
according to Nicholas Paldino's suggestion but it still appears to
have the same problem.

// here is the code that initiates the new thread
Thread batchEmails = new Thread(new
ParameterizedThreadStart(initiateEmailBatching));
batchEmails.SetApartmentState(ApartmentState.STA);
batchEmails.Start(isReprint);

// starting point of the new thread
private void initiateEmailBatching(object isReprint)
{
try
{
// run VBA macro that will create the email files in our
temporary directory
RunMacro("WMPrintEmailDDE", TemporaryDirectory,
(bool)isReprint);

}
catch (Exception ex)
{
throw new MessageSendingException("Cannot send the email,
please review your settings and try again.");
}

// batching is complete, notify our main thread of this
EmailBatchingComplete = true;
}

protected void RunMacro(string macroName, params object[] list)
{
object[] argumentList = new object[list.Length + 1];

argumentList[0] = macroName;

int counter = 1;

foreach (object current in list)
{
argumentList[counter] = current;

counter++;
}

_wordApp.GetType().InvokeMember("Run",
System.Reflection.BindingFlags.Default |
System.Reflection.BindingFlags.InvokeMethod, null, _wordApp,
argumentList);
}

 

[Willy Denoyette [MVP]]

Thanks for the info so far, here is all of the relevant code: I added
the line "batchEmails.SetApartmentState(ApartmentState.STA);"
according to Nicholas Paldino's suggestion but it still appears to
have the same problem.

The apartment state is irrelevant here, the COM object is an out-proc
server, so any apartment will do the job at the client.

// here is the code that initiates the new thread
Thread batchEmails = new Thread(new
ParameterizedThreadStart(initiateEmailBatching));
batchEmails.SetApartmentState(ApartmentState.STA);
batchEmails.Start(isReprint);

// starting point of the new thread
private void initiateEmailBatching(object isReprint)
{
try
{
// run VBA macro that will create the email files in our
temporary directory
RunMacro("WMPrintEmailDDE", TemporaryDirectory,
(bool)isReprint);

}
catch (Exception ex)
{
throw new MessageSendingException("Cannot send the email,
please review your settings and try again.");
}

// batching is complete, notify our main thread of this
EmailBatchingComplete = true;
}

protected void RunMacro(string macroName, params object[] list)
{
object[] argumentList = new object[list.Length + 1];

argumentList[0] = macroName;

int counter = 1;

foreach (object current in list)
{
argumentList[counter] = current;

counter++;
}

_wordApp.GetType().InvokeMember("Run",
System.Reflection.BindingFlags.Default |
System.Reflection.BindingFlags.InvokeMethod, null, _wordApp,
argumentList);
}

Your problem is that your thread terminates before any code gets actually
executed at the server (Word application) side.
So, you need to keep the thread alive until the macro has finished
processing, this is the key problem using Office macros from DCOM clients,
you don't know when the macro has finished. What you need to do is
1. use a callback, or
2. set a global flag when the word macro has finished. You can check this
flag from the client (before setting EmailBatchingComplete ) by polling at a
regular interval.

while(!ReadFinishedFlag())
{
Sleep(1000);
}
EmailBatchingComplete = true;

Willy.

 

[Omatase]

I already have this code. I put this directly below the code that is
spawning my thread

// message pump here to look for completion
while (!EmailBatchingComplete)
{
Thread.Sleep(1000);
}

That is the reason for the EmailBatchingComplete variable in my
original code in the first place.

Sorry I didn't include this in my last post.

 

[Willy Denoyette [MVP]]

I already have this code. I put this directly below the code that is
spawning my thread

// message pump here to look for completion
while (!EmailBatchingComplete)
{
Thread.Sleep(1000);
}

That is the reason for the EmailBatchingComplete variable in my
original code in the first place.

Sorry I didn't include this in my last post.

Not sure where you have placed this code nor where you initiate the new
thread, anyway it looks like the thread terminates prematurely, try adding
this:
batchEmails.Join();
after ...
batchEmails.Start(isReprint);

and see what happens.
If this doesn't solve your problem you'll have to post a complete sample
that illustrates your issue.
Willy.

 

[Omatase]

I have new information. After the "InvokeMember" method is called
below, if I wait 60 seconds, I get the following error:

ContextSwitchDeadlock was detected
Message: The CLR has been unable to transition from COM context
0x1a1900 to COM context 0x1a1a70 for 60 seconds. The thread that owns
the destination context/apartment is most likely either doing a non
pumping wait or processing a very long running operation without
pumping Windows messages. This situation generally has a negative
performance impact and may even lead to the application becoming non
responsive or memory usage accumulating continually over time. To
avoid this problem, all single threaded apartment (STA) threads should
use pumping wait primitives (such as CoWaitForMultipleHandles) and
routinely pump messages during long running operations.

I have displayed all of the code in my previous posts. Here it is
again all at once:

public bool EmailBatchingComplete = false;

private void sendEmail()
{
// here is the code that initiates the new thread
Thread batchEmails = new Thread(new
ParameterizedThreadStart(initiateEmailBatching));
batchEmails.SetApartmentState(ApartmentState.STA);
batchEmails.Start(isReprint);

// message pump here to look for completion
while (!EmailBatchingComplete)
{
Thread.Sleep(1000);
}
}

// starting point of the new thread
private void initiateEmailBatching(object isReprint)
{
try
{
// run VBA macro that will create the email files in our
temporary directory
RunMacro("WMPrintEmailDDE", TemporaryDirectory,
(bool)isReprint);

}
catch (Exception ex)
{
throw new MessageSendingException("Cannot send the email,
please review your settings and try again.");
}

// batching is complete, notify our main thread of this
EmailBatchingComplete = true;

}

protected void RunMacro(string macroName, params object[] list)
{
object[] argumentList = new object[list.Length + 1];

argumentList[0] = macroName;

int counter = 1;

foreach (object current in list)
{
argumentList[counter] = current;

counter++;
}

_wordApp.GetType().InvokeMember("Run",
System.Reflection.BindingFlags.Default |
System.Reflection.BindingFlags.InvokeMethod, null, _wordApp,
argumentList);
}

 

[Willy Denoyette [MVP]]

I have new information. After the "InvokeMember" method is called
below, if I wait 60 seconds, I get the following error:

ContextSwitchDeadlock was detected
Message: The CLR has been unable to transition from COM context
0x1a1900 to COM context 0x1a1a70 for 60 seconds. The thread that owns
the destination context/apartment is most likely either doing a non
pumping wait or processing a very long running operation without
pumping Windows messages. This situation generally has a negative
performance impact and may even lead to the application becoming non
responsive or memory usage accumulating continually over time. To
avoid this problem, all single threaded apartment (STA) threads should
use pumping wait primitives (such as CoWaitForMultipleHandles) and
routinely pump messages during long running operations.

I have displayed all of the code in my previous posts. Here it is
again all at once:

public bool EmailBatchingComplete = false;

private void sendEmail()
{
// here is the code that initiates the new thread
Thread batchEmails = new Thread(new
ParameterizedThreadStart(initiateEmailBatching));
batchEmails.SetApartmentState(ApartmentState.STA);
batchEmails.Start(isReprint);

// message pump here to look for completion
while (!EmailBatchingComplete)
{
Thread.Sleep(1000);
}
}

// starting point of the new thread
private void initiateEmailBatching(object isReprint)
{
try
{
// run VBA macro that will create the email files in our
temporary directory
RunMacro("WMPrintEmailDDE", TemporaryDirectory,
(bool)isReprint);

}
catch (Exception ex)
{
throw new MessageSendingException("Cannot send the email,
please review your settings and try again.");
}

// batching is complete, notify our main thread of this
EmailBatchingComplete = true;

}

protected void RunMacro(string macroName, params object[] list)
{
object[] argumentList = new object[list.Length + 1];

argumentList[0] = macroName;

int counter = 1;

foreach (object current in list)
{
argumentList[counter] = current;

counter++;
}

_wordApp.GetType().InvokeMember("Run",
System.Reflection.BindingFlags.Default |
System.Reflection.BindingFlags.InvokeMethod, null, _wordApp,
argumentList);
}

You have two possible issues here, note that I assume that this is a Windows
Forms application and that sendEmail is running on the main UI (STA) thread.
Issue 1:
What you are doing here[1] in sendEmail is blocking the Windows message
queue (you should never call sleep on a UI thread!). The CLR warns you about
this situation and issues a ContextSwitchDeadlock MDA when running in the
debugger.

[1]
while (!EmailBatchingComplete)
{
Thread.Sleep(1000);
}

you need to wait for the thread to finish while pumping the windows message
queue, one possible way to solve this is like:

while (!EmailBatchingComplete)
{
batchEmails.Join(1000);
}

Join does the exact pumping like there is mentioned in the MDA description
(CoWaitForMultipleHandles).
Note however that Join only performs some limited pumping, that means that
your UI might become non-responsive during the COM call. To solve this you
will have to change your design.

Issue 2:

The COM call is issued from a thread that entered a STA
(batchEmails.SetApartmentState(ApartmentState.STA);
), that means you also need to pump this thread's message queue which you
don't (can't). Only solution here is to initialize the thread to enter the
MTA, MTA threads don't need to pump explicitly.

Willy.

 

[Omatase]

Thank you all for your help. I was able to devote 2 more hours of
troubleshooting to this problem last night and while I still don't
really know what the problem is, I have found a work around.

The _wordApp variable I was using to "Run" the macro was instantiated
in the main thread, but I was trying to use it in the background
thread. This should be, as far as I know, acceptable use and if I put
a break point on the line _wordApp.GetType().InvokeMember..... I could
see that the _wordApp variable was correctly instantiated and looked
fine. However, if I reinstantiate the _wordApp variable in the
background thread before calling _wordApp.GetType().InvokeMember.....
the VBA code everything works as expected.

So, I don't know what caused the problem, but this work around for me
is one that I can live with just fine. If anyone has any insight as to
why I had the problem in the first place it would be much appreciated.

 

[Willy Denoyette [MVP]]

Thank you all for your help. I was able to devote 2 more hours of
troubleshooting to this problem last night and while I still don't
really know what the problem is, I have found a work around.

The _wordApp variable I was using to "Run" the macro was instantiated
in the main thread, but I was trying to use it in the background
thread. This should be, as far as I know, acceptable use and if I put
a break point on the line _wordApp.GetType().InvokeMember..... I could
see that the _wordApp variable was correctly instantiated and looked
fine. However, if I reinstantiate the _wordApp variable in the
background thread before calling _wordApp.GetType().InvokeMember.....
the VBA code everything works as expected.

So, I don't know what caused the problem, but this work around for me
is one that I can live with just fine. If anyone has any insight as to
why I had the problem in the first place it would be much appreciated.

Actually this is what I tried to explain since the start of this thread ;-)
As I suggested in my first reply - "This should work provided you call the
"Run" method from the _wordApp creating thread", I meant that as long as you
call the COM method on the same thread on which the COM object was created
everything should be fine.
If however you create a COM object on a different thread from the thread
that uses the reference to call methods on the object , you'll have to deal
with interface marshaling issues.

In your scenario you have:

Thread T1 (UI thread) STA:
holds a reference to COM object "_wordApp"
Thread T2 background thread (STA or MTA no matter):
calls COM method (late bound).

Thread T2 cannot directly call a method on that the interface pointer
("_wordApp), the ITF pointer must be marshaled (done by the CLR), such that
the calling thread (T2) receives a proxy to communicate with the stub on T1
using "Windows" message passing. Now T1 runs in a STA and hosts the UI, that
means that T1 must pump the Windows message queue , for both the UI and COM
messages, something you failed to do with your "Sleep" loop. A solution
(part of) for this is to use Join instead of Sleep.

A better option, which eliminates all ITF marshalling issues, is like you
did by creating the object on the T2.
Sure, here you'll have only one thread that can use the object (Word server)
at a time, which is a good thing anyway as Office was not designed to be
"automated" from multiple threads simultaniously....

Willy.