Office VBA "potential" with .NET multithreading

I've been air-dropped into a project heavy with Excel and Access VBA. (in a desperate attempt to replace someone quitting) The main batch job picks up header data from database and creates Excel workbooks from pre-written templates. These templates contain VBA macros that will construct the worksheets with data.

Because there are like 20,000 workbooks to create at one go, the batch job program (.NET) spawns threads with each tasked with creating workbooks. The threads cycle until there are no more "NOTRUN" records in database (meaning "process me!"). It was discovered in production environment stress test that
1. a particular macro will be reported not found in the Excel template (but it _is_ there).
2. or another macro will encounter Type mismatch errors when filling the worksheets with data. But the data is correct, since running a second time will have no error.

I do not have the macro code here nor have experience with VBA to describe it quite elaborately, but what I'd need to know at this stage is whether Excel COM objects (an Excel.exe process for each thread) can truly function properly in a multithreaded scenario like this? For the above problems happen randomly and infrequently.

I have learnt to use the Marshal.ReleaseComObject() on the WorkBook object after each iteration (if that was a potential memory leak), but apparently that is not the problem since the above errors are still occuring.

the ReleaseComObject should be merely a wrapper to Release() on the COM IUnknown interface and since memory management with COM is through reference counting, the situation can be sticky.

I'm not sure if this helps, but have you explored MTA vs STA?

Yes the ReleaseComObject() method is precisely to decrement the COM reference count. The program is already set to run MTA, however, I have found a thread in MSDN forums stating this may be an attempt in vain as all Excel COM components are written to run STA.

An update on the situation: Microsoft has expressed a firm NO-WAY-IT-GONNA-WORK statement.

it won't be stable even when we run multiple processes (single thread) each under a different user account, although it did bring down the frequency of the errors.

Since we cannot overwrite the (Main contractor's flawed) design decision of generating multiple workbooks using the existing templates' VBA functions, we have written "watchdog" timers to check if a particular worker thread has hung due to one of those errors and therefore abort the thread, kill the associated Excel.exe, and start afresh.

very interesting. it's one of those stories that says just because you can do it doesn't mean you should do it.

For some reasons, the financial people tend to derive extreme joys from Macros (they don't know the term VBA). Sure, macros get the job done and that's what matters most of the time. But when a developer takes a look at macros, he cries.

feelite:

just because you can do it doesn't mean you should do it.

Actually, it cannot be done in the first place. Not in this way.