How to invoke excel macros parallel

Question

0.00/5 (No votes)

See more:

Hi Friends,
I need to run excel macro multiple time with some changes in the input. since macro takes more time to execute i need to open multiple instance of excel and run macros parallely. How can i achieve this? please help me with some ideas.

Current timing
one macro run will take ~10 mint. i need to call this macro in a loop with count 25.so approx it taking 2hrs. my aim is to reduce to 30 mint

C#

 public static Excel.Application oExcel;
 oExcel = new Excel.Application();
            object oMissing = System.Reflection.Missing.Value;
            oExcel.Visible = false;
            Excel.Workbooks oBooks = oExcel.Workbooks;
            Excel.Workbook oBook = null;
            oBook = oBooks.Open(filepath, oMissing, oMissing,
                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

 for (int i = 0; i < 25; i++)
{
Excel.Worksheet sheet = (Excel.Worksheet)oExcel.Sheets[1];
 sheet.Cells[8, 3] = i; 
 MacroRun.RunExcelMacro(oExcel, "runTestCases");
}

What I have tried:

C#

private static void RunMacro(object oApp, object[] oRunArgs)
       {
           oApp.GetType().InvokeMember(Constants.RUN,
               System.Reflection.BindingFlags.Default |
               System.Reflection.BindingFlags.InvokeMethod,
               null, oApp, oRunArgs);
       }
       public static void RunExcelMacro(Excel.Application oExcel, string macroname, string paramaters = "")
       {
           // Run the macros.
           if (paramaters == "")
               RunMacro(oExcel, new Object[] { macroname });
           else
               RunMacro(oExcel, new Object[] { macroname, paramaters });
       }

Posted 17-Nov-16 6:48am

jinesh sam

Updated 17-Nov-16 7:16am

Add a Solution

Comments

Patrice T 17-Nov-16 12:58pm

What is doing the macro ?

jinesh sam 17-Nov-16 13:00pm

some business logic... i need to validate the output from excel and API

Karthik_Mahalingam 17-Nov-16 22:55pm

Always use Reply button, to post Comments/query to the user, so that the user gets notified and responds to your text.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2016-11-17T07:16:00

Your only option is to create separate instances of Excel.Application. Each one will have to open the workbook you're working with, each it's own copy of the file, take your different inputs and run their copy of the macros.

You can NOT execute macros in "parallel" in a single instance of Excel.

Keep in mind that this may not give you the performance you're looking for. We have no idea in which domain(s) the performance bottleneck lies so it's impossible for anyone to give you any advice on any additional changes.