如何调用excel宏并行 [英] How to invoke excel macros parallel

问题描述

嗨朋友们，

我需要多次运行excel宏，并在输入中进行一些更改。因为宏需要更多的时间来执行，我需要打开多个excel实例并并行运行宏。我怎样才能实现这一目标？请帮我一些想法。



当前时间

一次宏观运行需要~10美分。我需要在一个循环中调用这个宏，计数为25.so约需要2小时。我的目标是减少到30美分

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

 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:

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 });
       }

推荐答案

您唯一的选择是创建Excel.Application的单独实例。每个人都必须打开你正在使用的工作簿，每个人都有自己的文件副本，接受你不同的输入并运行他们的宏副本。



您不能在单个Excel实例中以并行方式执行宏。



请记住，这可能无法为您提供所需的性能。我们不知道性能瓶颈属于哪个域，因此任何人都无法就任何其他更改向您提供任何建议。

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.

这篇关于如何调用excel宏并行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！