Create And Run Excel Macro At Runtime in C#

By Robbe D. Morris

Printer Friendly Version

Robbe Morris
Robbe & Melisa Morris
  Download C# Source Code
You've been tasked with writing a Windows Form application using the .NET Framework that interacts with Microsoft Excel.  If you are new to working with Excel in .NET and are having trouble learning how to format certain cells or cell ranges, then this little tip will come in handy.  Rather than scour the internet hoping to find the correct set of constants or method syntax for formatting your spreadsheet, use the built in tools in Excel to write the familiar VBA code for you.  Then, you can paste the VBA code generated by Excel into the sample C# code below and run it dynamically at runtime.
Using Excel's Macro recorder is really simple.  Via the Menu, click Tools/Macro/Record New Macro.  Use the default Macro name and click Ok.  At this point, start manually formatting the spreadsheet to make it look exactly as you would expect.  Then, click the Stop button on the recorder dialog floating on top of the spreadsheet.  Via the Menu, click Tools/Macro/Macros, select your macro and click Edit.  Copy and Paste this code into your C# code.  Using my sample code, you would paste it into the method .GetMacro() and reformat the Macro code to fit into the StringBuilder .Append method as I've done.  Notice that I've hardcoded newline characters at the end of each VBA source code line.
As part of the dynamic creation of the Excel workbook, we pass in the results of .GetMacro() and its prenamed Sub FormatSheet.  Read the inline comments carefully in the C# code below to learn how we ran the sample Macro and converted the font color to red for some of the cells of sample data.  Keep in mind, you can make modifications to .GetMacro() to further customize which cells are formatted and under what conditions.  This can come in pretty handy if your application has to implement user specific color branding or other custom formatting features.


Sample Code
using System;
using System.IO;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text;
 
namespace ConsoleApplication1
{
 
   class Class1
   {
 
     [STAThread]
     static void Main(string[] args)
     {
       string MyFile = Path.GetFullPath(".") + @"\sample.xls";
       CreateWorkbook(MyFile,GetMacro());  
       Console.WriteLine("File Saved to " + MyFile);
       Console.ReadLine();
     }

     #region Get Macro
     private static string GetMacro()
     {

        StringBuilder sb = new StringBuilder();
		  
        sb.Append("Sub FormatSheet()" + "\n");
        sb.Append("  Range(\"A6:J13\").Select " + "\n");
        sb.Append("  Selection.Font.ColorIndex = 3" + "\n");
        sb.Append("End Sub");

        return sb.ToString();
     }
     #endregion

     #region Create Workbook
     private static void CreateWorkbook(string FileName,string Macro)
     {
			 
       Excel.Application xl = null;
       Excel._Workbook wb = null;
       Excel._Worksheet sheet = null;
       VBIDE.VBComponent module = null;
       bool SaveChanges = false;
 
       try
       {

         if (File.Exists(FileName)) { File.Delete(FileName); }
  
         GC.Collect(); 

         // Create a new instance of Excel from scratch

         xl = new Excel.Application();
         xl.Visible = false;
			 
        // Add one workbook to the instance of Excel

         wb = (Excel._Workbook)(xl.Workbooks.Add( Missing.Value ));

       // Get a reference to the one and only worksheet in our workbook

         sheet = (Excel._Worksheet)wb.ActiveSheet;
				 
       // Fill spreadsheet with sample data

         for(int r = 0;r<20;r++)
         {

           for(int c=0;c<10;c++)
           {
             sheet.Cells[r + 1, c+1] = 125; 
           }

         }

        // Dynamically create a code module and load it with the string we formatted
        // in the .GetMacro() method above.

         module = wb.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
         module.CodeModule.AddFromString(Macro);
                
         // Run the named VBA Sub that we just added.  In our sample, we named the Sub FormatSheet

         wb.Application.Run("FormatSheet",Missing.Value,Missing.Value,Missing.Value,
                             Missing.Value,Missing.Value,Missing.Value,Missing.Value,
                             Missing.Value,Missing.Value,Missing.Value,Missing.Value,
                             Missing.Value,Missing.Value,Missing.Value,Missing.Value,
                             Missing.Value,Missing.Value,Missing.Value,Missing.Value,
                             Missing.Value,Missing.Value,Missing.Value,Missing.Value,
                             Missing.Value,Missing.Value,Missing.Value,Missing.Value,
                             Missing.Value,Missing.Value,Missing.Value);

        // Let loose control of the Excel instance

         xl.Visible = false;
         xl.UserControl = false;

       // Set a flag saying that all is well and it is ok to save our changes to a file.

         SaveChanges = true;

      //  Save the file to disk

         wb.SaveAs(FileName,Excel.XlFileFormat.xlWorkbookNormal,
                   null,null,false,false,Excel.XlSaveAsAccessMode.xlShared,
                   false,false,null,null,null);
				 
     }
     catch(Exception err) 
     {
        String msg;
        msg = "Error: ";
        msg = String.Concat(msg,err.Message);
        msg = String.Concat(msg," Line: ");
        msg = String.Concat(msg,err.Source); 
        Console.WriteLine(msg);
     }
     finally
     {

       try
       {
         // Repeat xl.Visible and xl.UserControl releases just to be sure
         // we didn't error out ahead of time.

         xl.Visible = false;
         xl.UserControl = false;
         // Close the document and avoid user prompts to save if our
         // method failed.
         wb.Close(SaveChanges,null,null);
         xl.Workbooks.Close();
       }
       catch { }

       // Gracefully exit out and destroy all COM objects to avoid hanging instances
       // of Excel.exe whether our method failed or not.

       xl.Quit();
				
       if (module != null) { Marshal.ReleaseComObject (module); }
       if (sheet !=null)   { Marshal.ReleaseComObject (sheet); }
       if (wb !=null)      { Marshal.ReleaseComObject (wb); }
       if (xl !=null)      { Marshal.ReleaseComObject (xl); }
				
        module = null;
        sheet=null;
        wb=null;
        xl = null;
        GC.Collect(); 
     }

    }
    #endregion

  }
}


Robbe has been a Microsoft MVP in C# since 2004.  He is also the co-founder of NullSkull.com which provides .NET articles, book reviews, software reviews, and software download and purchase advice.