How can I embed some VBA code into a spreadsheet created using ClosedXML?

Question

I am using ClosedXML to generate spreadsheets from C# (asp.net-mvc) and it works great. I have one additional requirement so I wanted to get some feedback on how I could achieve this.

I want to save as a macro enabled workbook and when I just give it a "xlsm" extension it doesn't seem to open (versus a xlsx). Here is my code:

public ActionResult ExportExcel()
{
    MemoryStream stream = nModel.GenerateSS();
    return File(stream, @"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml", "MySS.xlsx");
}

but if I try to do this:

public ActionResult ExportExcel()
{
    MemoryStream stream = nModel.GenerateSS();
    return File(stream, @"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml", "MySS.xlsm");
}

Excel complains when trying to open.

Then, assuming I can do #1, I need to be able to take some VBA (assuming just a hard-coded function) and insert that into a module or workbook, so when someone opens the spreadsheet and clicks on Macros, they can run the macros. From googling, that doesn't seem supported by ClosedXML so I was curious if anyone has any alternative ways to achieve this?

Answer 1

The only way I know for inserting code to a VBA Project is to use Office.Interop.Excel along with Microsoft.Vbe.Interop (not sure about ClosedXML and other 3rd party...but still, wanted to share)

But since you are asking for an alternative this is how I would go about;

Creating a workbook and inserting a macro to it using C#

---

You need to allow programmatic access to VBA Project in Excel.

• (Excel) File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings -> Trust Access to the VBA Project Object Model

In your C# Solution add COM references to

• Microsoft Visual Basic for Applications Extensibility 5.3

• Microsoft Excel 14.0 Object Library

Add using directives to your C# code behind file

using Microsoft.Office.Interop.Excel;
using System.Reflection;
using Microsoft.Vbe.Interop;
using System.Runtime.InteropServices;

Now follow the code and comments

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = true;
Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

VBProject vbProj = wb.VBProject; // access to VBAProject
VBComponent vbComp = vbProj.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule); // adding a standard coding module
vbComp.CodeModule.DeleteLines(1, vbComp.CodeModule.CountOfLines); //emptying it

// this is VBA code to add to the coding module
string code = "Public Sub HelloWorld() \n" +
                "    MsgBox \"hello world!\" \n" +
                "End Sub";

// code should be added to the module now
vbComp.CodeModule.AddFromString(code); 

// location to which you want to save the workbook - desktop in this case
string fullPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\macroWorkbook.xlsm";

// run the macro
xlApp.Run("HelloWorld");

// save as macro enabled workbook
wb.SaveAs(Filename: fullPath, FileFormat: XlFileFormat.xlOpenXMLWorkbookMacroEnabled);
xlApp.Quit();

In the above code;

you create an host Excel application, add a workbook. Access VBA Project Object module, add a new standard coding module to the VBA Project, write a VBA macro to that module. Application.Run("HelloWorld") simply calls the macro - note you can comment out this section if you don't want the box to pop-up. Then in the end you save the workbook as a Macro Enabled Workbook to a location specified in fullPath variable.

PS. Please note I haven't added any error handling.

For additional tips on C# and VBA see this blog Hope this helps :)