EPPlus Adding VBA Code Corrupts resulting xlsx file

Question

I'm creating an excel file using EPPlus from a datatable and saving it to a file, all fairly standard. This works without any hassles. However, if I create a vba project, and add any vba code, the resulting .xlsx file is suddenly corrupted. When try and open it with 2013, you get the standard error.

"Excel cannot open the file 'xyz.xlsx' because the file format or file extension is not valid. Verify that the file extension matches the format of the file.

If I change the extension to .xls, the file opens ok, and the script runs fine. I'm on the latest beta of Epplus (4.5.0.1). Should I just change the file extension and stick to .xls, or is there something amiss?

ExcelPackage.Workbook.CreateVBAProject()
ExcelPackage.Workbook.CodeModule.Code = "'Script is here"
Dim FInfo as new FileInfo("C:\Myfiles\ThisFile.xlsx")
ExcelPackage.SaveAs(FInfo)

Yahya Hussein Yahya Hussein · Accepted Answer · 2017-12-27T11:56:50

You can not add VBA code to XLSX file format. Take a look at here

.xlsx:

The default XML-based file format for Excel 2007-2013. Cannot store Microsoft Visual Basic for Applications (VBA) macro code or Microsoft Office Excel 4.0 macro sheets (.xlm).

Also take a look at here to see differences between different formats:

XLSM If you include VBA macros in your XLSX file, you will be asked to change the file extension to XLSM. Only that way you can save your macro within the Excel file (there are other file types possible, e.g. XLSB – but you can’t use XLSX for VBA macros)

Save your file as XLSM and it should work.

EPPlus Adding VBA Code Corrupts resulting xlsx file

1 Answers