none
How to iterate Excel worksheets in Excel workbook to run macro or command? RRS feed

  • Question

  • This link https://support.microsoft.com/en-us/kb/306683?wa=wsignin1.0 shows only run the macro for the first worksheet. I need help on iterating Excel worksheets in Excel workbook to run macro for each worksheet.

    In the link, the excel object does not specify the worksheet.

    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open(filename.xlsm);
    RunMacro(sheet, new Object[] { "'" + dataFile.FilePath.Replace(".xlsx", ".xlsm") + "'!Macro1" });
    
    private static void RunMacro(object excel, object[] runArgs)
    {
      excel.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, excel, runArgs);
    }

    I tried the following but got error on sheet in RunMacro method.

    foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in wb.Worksheets)
    {
       RunMacro(sheet, new Object[] { "'" + dataFile.FilePath.Replace(".xlsx", ".xlsm") + "'!Macro1" });
    }


    Thanks.

    DanYeung




    • Edited by danyeungw Wednesday, June 3, 2015 11:07 PM
    Wednesday, June 3, 2015 8:26 PM

Answers

  • Hi DanYeung,

    Which version of Excel are you using? As far as I know, from Excel 2007, we can call the Application.Run to run the macro in Excel application directly. And we can add the sheet name before the macro name like code below:

      string fileName = @"C:\book.xlsm";
                Application excelApp = new Application();
                excelApp.Visible = true;
                Workbook aWorkbook = excelApp.Workbooks.Open(fileName);
               
                excelApp.Run("Sheet1.test");
                excelApp.Run("Sheet2.test");

    In addition, here is document link for this function:

    Application.Run Method (Excel)

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, June 4, 2015 5:35 AM
    Moderator
  • I figured it out.

    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open(dataFile.FilePath);

    foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in excel.Worksheets) { sheet.Activate(); excel.Run("'" + xlsmFileName + "'!Macro1"); }

    Thanks.


    Thursday, June 4, 2015 3:29 PM

All replies

  • Hi DanYeung,

    Which version of Excel are you using? As far as I know, from Excel 2007, we can call the Application.Run to run the macro in Excel application directly. And we can add the sheet name before the macro name like code below:

      string fileName = @"C:\book.xlsm";
                Application excelApp = new Application();
                excelApp.Visible = true;
                Workbook aWorkbook = excelApp.Workbooks.Open(fileName);
               
                excelApp.Run("Sheet1.test");
                excelApp.Run("Sheet2.test");

    In addition, here is document link for this function:

    Application.Run Method (Excel)

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, June 4, 2015 5:35 AM
    Moderator
  • The marco is in the workbook. It works with following, but only the first worksheet. 

    excelApp.Run("'" + xlsmFileName + "'!Macro1");

    When I used 

    excel.Run("'" + sheetName + "'!Macro1");

    , I got an error Macro1 not found.

    Maybe I have to activate the worksheet then run the macro? Like 

    foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in excel.Worksheets)
    {
        //Activate the worksheet
        excel.Run("'" + xlsmFileName + "'!Macro1");
    }

    How to activate the worksheet?

    Thanks.

    Thursday, June 4, 2015 2:38 PM
  • Excel 2013.
    • Edited by danyeungw Thursday, June 4, 2015 3:00 PM
    Thursday, June 4, 2015 3:00 PM
  • I figured it out.

    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open(dataFile.FilePath);

    foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in excel.Worksheets) { sheet.Activate(); excel.Run("'" + xlsmFileName + "'!Macro1"); }

    Thanks.


    Thursday, June 4, 2015 3:29 PM