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

Question

I use ClosedXML to create tables from C # (asp.net-mvc) and it works fine. I have one additional requirement, so I wanted to get some feedback on how I could achieve this.

I want to save as a book with macro support, and when I just give it the extension "xlsm", it does not seem to open (compared to 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 that I can do # 1, I need to be able to take some VBA (assuming only a hard-coded function) and paste it into a module or workbook, so when someone opens a spreadsheet and clicks on Macros, they can run macros. From googling, which seems not to be supported by ClosedXML, so I was curious if anyone has any alternative ways to achieve this?

Answer 1

The only way I know to embed code in a VBA project is to use Office.Interop.Excel along with Microsoft.Vbe.Interop (not sure if ClosedXML and the other third party ... but still want to split)

But since you are asking for an alternative, here is how I will:

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

---

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

• (Excel) File → Settings → Trust Center → Trust Center Settings → Macro Settings → Trust access to the VBA project object model

In your C # solution, add COM links to

• Microsoft Visual Basic for Application Extensibility 5.3

• Microsoft Excel Object Library 14.0

Add using directives to your C # code behind the 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 application for the Excel host, add a workbook. Open the VBA Project Object module, add a new standard coding module to the VBA project, write a VBA macro to this module. Application.Run("HelloWorld") just calls the macro, you can comment on this section if you do not want the window to pop up. Then at the end you save the book as a book with macro support at the location specified in the fullPath variable.

PS. Please note that I did not add error handling.

For more tips on C # and VBA, see this blog. Hope this helps :)

Answer 2

ClosedXML does not currently support direct VBA writing.

However, you can manually add the .bin file containing the predefined VBA macros / functions to the existing .XLSX table programmatically. Record all your VBA macros in advance and save the file as an .XLSM file. Extract the contents to a folder and you will have vbaProject.bin containing all your code. To load the VBA project correctly, workbook.xml requires the following additions:

 <Default Extension="bin" ContentType="application/vnd.ms-office.vbaProject"/> <Override PartName="/xl/workbook.xml" ContentType="application/vnd.ms-excel.sheet.macroEnabled.main+xml"/> 

vbaProject.bin will need to be placed in the folder with the "xl" folder, and for the workbook you will need a GUID and an alias codeName (the default is "TheWorkbook"). All sheets also need the codeName name (matching the sheet name) for macro calls to really work when referencing named sheets.

John McNamara created Perl authentication that is worth checking here: http://blogs.perl.org/users/john_mcnamara/2012/12/adding-macros-to-excelwriterxlsx.html

Scanning for assembly in IlSpy, this should be pretty easy to implement in ClosedXML, so if you have success, it would be nice to push your input into the main project.

Answer 3

 return File(stream, @"application/vnd.ms-excel.sheet.macroEnabled.12", "MySS.xlsm"); 

According to this link, the MIME type for the macro workbook Office Excel 2007 (.xlms) is

application/vnd.ms-excel.sheet.macroEnabled.12 . application/vnd.ms-excel.sheet.macroEnabled.12 .

You saw this post in the ClosedXml codeplex project regarding macro support.