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

asp.net-mvc, c#, closedxml, excel, openxml

Solution

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 :)

Problem

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?

Original source