Multithreading to "speed" up Excel automation?

Hi there,

I'm wondering if it is possible to use multithreading to do some excel automation, while the current application continues, then when the macro's are finished the thread would end and a message box would pop up saying the macro was finished.

I have never done multithreading in windows, the most applicable thing I've done is creating children processes in linux using C...

As such, can anyone suggest a good intro tutorial to do this in .net or perhaps one for a general explanation of how multithreading might work in Windows...


Thanks

Frank

Not an easy topic, but within the VB.Net Tutors Corner (http://www.xtremevbtalk.com/forumdisplay.php?f=102), there is an article on Multithreading in VB.Net (http://www.xtremevbtalk.com/showthread.php?t=159864) by Excaliber.

I'm not sure what process that you have in mind, but running Excel directly with VBA instead of using Automation will run 50x faster or more. A 60 minute routine can be cut down to a minute and change (and sometimes less).

Other techniques include pulling in Range.Value's into an Array and processing the Array instead of directly processing cells one-by-one. This can improve speed by 50x over VBA speed! Well, it would in VB6... In VB.Net, the improvement will be a lot less impressive, unfortunately, and probably requires that you use 'Option Strict Off'. :(

In short, I don't have a lot of good answers for you, but maybe you could lay out what you are trying to do...? Maybe a separate, small application that runs and then opens up a MsgBox when it is done (alerting the User) is sufficient? Not very exciting, I know, but having a Master App controlling a Multi-Threaded solution using .Net to control Excel via the COM Interop, well..., I think it's gunna get hairy!

I would try to keep it simple here. This is especially true when using .Net to control Excel. You can be more ambitious with VB6 or VBA because it runs natively and is much smoother to develop. Much.

Any of this help, or am I just scaring you? :(
Mike

Thanks for the reply Mike :)

Essentially, i have a front end to an excel sheet. Shows some tables, a few options that it reads in from certain sheets in a workbook and puts them together in a nice organized fashion. The user edits them in the VB.NET app then upon hitting the "run" button, the first workbook the values where read from is copied and the new values are placed in the new copied workbook. Now, a few macro's are run. So in essence, all i'm doing is populating a few cells then calling the VBA macro to deal with it. ie. it's a legacy excel vba app and we're just trying to reuse that as a complete overhaul in vb.net might be nice, but there isn't enouph time to do that, ATM.

But, the macro does so much stuff that even run from within Excel itself (not automateD) it takes awhile...but adding the .Net automation and all that overhead it takes EVEN longer. Which kinda hurts the usability of the application...

The easiest solution I thought, was to spawn a child process or a new thread (like I said i'm not familiar with this) that will create the new workbook, copy the values into the excel sheet and run the appropriate macros. All the while, the user can start working on new settings for the NEXT worksheet. Meanwhilst, the already existing excel instance is churning away.

I hope that clarifies it a bit more and thanks again.

Frank

I skimmed that Multithreading article last night... it doesn't look so bad! Basically, .Net handles the "splitting" for you and encapsulates different processes, so this may, in fact, be quite doable. That said, I've not read it that thoroughly and never actually tried it, but that article would seem the place to start.

The other way to go would seem to be to allow the User to set all the settings in advance, and then do a batch run. Not as slick, but easier. But if the goal is to "really do it right" (and obstacles be damed! :p) then I would definitely try giving that tutorial a read...