C# Calling Macro getting very slow using Interop

Sudip Bhatt 2,271 Reputation points
2021-02-04T16:28:38.827+00:00

This is code which calling Macro but it is taking 15 minute time when i am running the same macro from excel then it is taking 4 minute approx. please guide me how to optimize as a result time can be shorten.

using interop version 15

            public static void fn_run_macro(string _macro_path, string brokermodel, string brkSheet, string _macroFor, string _CompSSFile = "", string _Custom = "", string _imageFile = "")
            {
                try
                {
                    string _path_macro = _macro_path;
                    string _path = brokermodel;

                    Excel.Application book = null;
                    Excel.Workbooks workbooks = null;
                    Excel.Workbook macroWorkbook = null;
                    Excel.Workbook destinationWorkbook = null;
                    string macrofileName = Path.GetFileName(_macro_path);
                    try
                    {
                        book = new Excel.Application();
                        workbooks = book.Workbooks;
                        macroWorkbook = workbooks.Open(_path_macro);
                        destinationWorkbook = workbooks.Open(_path, ReadOnly: false);

                        book.Run("'" + _macro_path + "'!MyMacroFunction");

                         workbooks.Close();
                        macroWorkbook.Close();
                        destinationWorkbook.Close();
                    }
                    catch (Exception ex)
                    {
                        // throw ex; // the finally will be executed before this is thrown
                    }
                    finally
                    {
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(macroWorkbook);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(destinationWorkbook);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(book);

                        macroWorkbook = null;
                        destinationWorkbook = null;
                        workbooks = null;
                        book = null;
                    }
                }
                catch
                {

                }
            }

Calling above function this way

        **Util.fn_run_macro(strPreTreatmentPath + "Horizontal CSM 082113b.xlsm", strTempPreTreatmentPath + "RVNC 01-28-2021 Pre Q4 Update.xlsx", "", "2010AFY");**

Please guide me any other free library available which can call macro and take less time. Thanks

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,363 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Timon Yang-MSFT 9,576 Reputation points
    2021-02-05T08:12:51.67+00:00

    I know that using Interop will be slower at certain times, but the difference is still a little beyond my expectations. I have some doubts that calling Macro is really wasting so much time?

    Please check it with StopWatch, like this:

        Stopwatch stopwatch = Stopwatch.StartNew();  
        application.Run("'" + macrofileName + "'!RoundedRectangle1_Click");  
        stopwatch.Stop();  
        Console.WriteLine(stopwatch.ElapsedMilliseconds);  
    

    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments