none
Using C# to run excel macros RRS feed

  • Question

  • So I have process already built in excel to update the document with information from my database.  I have been using C# to do several other daily tasks, and adding this one to the code is the next step.  My code for this is below:

    public static void UpdateFile(string Filename, DateTime AsOfDate)
            {
                ExcelWrapper excel = new ExcelWrapper();
                excel.OpenFile(Filename, false);
                string xll = ConfigurationManager.AppSettings["FILE.xll"];
                excel.LoadXll(xll);
                excel.Application.Range["NamedRange"].Value = AsOfDate;
                excel.Application.Run("BuiltMacro");
                excel.Application.Calculation =
                    Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationAutomatic;
                excel.CloseFile(Filename, true);
                excel.Application.Quit();
            }

    Excel wrapper is another command that opens excel with certain settings (like visible = false) so that the program runs uniformly.  The xll file that gets loaded is needed in order for the spreadsheet to use certain custom functions that have been built.  BuiltMacro is a simple sub that calls 5 distinct subs.  The issue I am having is 3 of the subs aren't being executed.  The only thing that I can think of, is those 3 subs eventually call routines in C# (via the xll).  Any ideas on what is happening?

    I am very new to C#, so any idea could be very helpful.  Thanks.

    Wednesday, April 22, 2015 2:34 PM

Answers

  • In case anyone is looking, the issue was my ExcelWrapper function turns AutoCalculation to manual.  So those 3 macros weren't running because there were calculated values that needed to change in order to kick them off.  I added one line and it worked.
    public static void UpdateFile(string Filename, DateTime AsOfDate)
            {
                ExcelWrapper excel = new ExcelWrapper();
                excel.OpenFile(Filename, false);
                string xll = ConfigurationManager.AppSettings["FILE.xll"];
                excel.LoadXll(xll);
                excel.Application.Range["NamedRange"].Value = AsOfDate;
                excel.Application.Calculate();
                excel.Application.Run("BuiltMacro");
                excel.Application.Calculation =
                    Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationAutomatic;
                excel.CloseFile(Filename, true);
                excel.Application.Quit();
            }

    • Marked as answer by DrJonZoidberg Thursday, April 23, 2015 2:43 PM
    Thursday, April 23, 2015 2:43 PM

All replies

  • The issue I am having is 3 of the subs aren't being executed.

    What subs are you talking about? Could you be more specific?

    Wednesday, April 22, 2015 3:50 PM
  • The macro built in Excel VBA "BuiltMacro" calls 5 subs.  3 of those subs are what isn't running.  The code is unfortunately too long and too much interlinking is happening to send all the code at once.
    Wednesday, April 22, 2015 4:22 PM
  • In case anyone is looking, the issue was my ExcelWrapper function turns AutoCalculation to manual.  So those 3 macros weren't running because there were calculated values that needed to change in order to kick them off.  I added one line and it worked.
    public static void UpdateFile(string Filename, DateTime AsOfDate)
            {
                ExcelWrapper excel = new ExcelWrapper();
                excel.OpenFile(Filename, false);
                string xll = ConfigurationManager.AppSettings["FILE.xll"];
                excel.LoadXll(xll);
                excel.Application.Range["NamedRange"].Value = AsOfDate;
                excel.Application.Calculate();
                excel.Application.Run("BuiltMacro");
                excel.Application.Calculation =
                    Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationAutomatic;
                excel.CloseFile(Filename, true);
                excel.Application.Quit();
            }

    • Marked as answer by DrJonZoidberg Thursday, April 23, 2015 2:43 PM
    Thursday, April 23, 2015 2:43 PM