Export data to Excel Macro-enabled through Open XML

Question

I have an Excel Sheet which I am using as a template file for exporting data.

The Excel Sheet is XLSM file and has few codes written in it in VBA.

Each time the file is copied and renamed with time stamp and data should be written to copied xlsm file but it is not writing the data.

I am using Open XML library for this.

The same is working if I use xlsx template file.

Is it not possible to write on xlsm Excel Macro-enabled through Open XML?

If yes, Any instructions to keep in mind.

Answer 1

The code in this answer will copy any excel file and switch it to a Macro Enabled Workbook. I have tested it against a simple Excel 2016 Workbook and Macro Enabled Workbook it creates a new file SaveMEW.xlsm in the same directory that the source file lives. Make sure the full path of the source file is put into `_sourceFile variable.

    var _sourceFile = " PATH TO YOUR TEMPLATE FILE ";

    using (var templateFile = File.Open(_sourceFile, FileMode.Open, FileAccess.Read))
        {
            using (var stream = new MemoryStream())
            {
                templateFile.CopyTo(stream);
                using (var spreadsheetDocument = SpreadsheetDocument.Open(stream, true))
                {                
                    spreadsheetDocument.ChangeDocumentType(SpreadsheetDocumentType.MacroEnabledWorkbook);
                }
                byte[] buffer = stream.ToArray();
                MemoryStream ms = new MemoryStream(buffer);
                FileStream file = new FileStream(System.IO.Path.GetDirectoryName(_sourceFile) + "/SaveMEW.xlsm",
                    FileMode.Create, FileAccess.Write);
                ms.WriteTo(file);
                file.Close();
            }
        }