0

I know that it is not possible to create new macro using NPOI, but according to the document, already existed macro should be preserved on copy:

Macros can not be created. The are currently no plans to support macros. However, reading and re-writing files containing macros will safely preserve the macros. Recent versions of Apache POI support extracting the macro data via VBAMacroExtractor and VBAMacroReader

However, when I tried to copy a xlsm file using NPOI, I ran into the following problem:

  • In my xlsm file, I have a button with an onclick function named "CopyDetails_Click".
  • When I copy that file, all other macro in the new file still works as expected, but in Macro Registration menu of my button, the name of the onclick function is changed to a seemly random value, for example "Button1_Click".
  • Because of that, the button no longer works, I have to manually changed the onclick function name back to "CopyDetails_Click". This seems to be the only problem I have, all other macro functions works as expected.

Is this a bug of Apache POI or a bug of NPOI, and how do I keep the correct onclick function name when I copy a xlsm file?

I'm using .Net Framework 4.6.2, NPOI 2.3.0 and trying to copy an Excel 2013 file.

Update 1: I found out that only buttons placed directly on the sheet are effected by this error. If I place my buttons inside a form then its macro name will be preserved after copy, and they will work as expected.

Community
  • 1
  • 1
duongntbk
  • 620
  • 4
  • 25
  • NPOI doesn't have any methods to correct this and it's likely an error in the library, unfortunately. May just have to rename your macro to use Button1_Click and call it a day. ¯\_(ツ)_/¯ – justiceorjustus Jun 30 '17 at 17:58
  • In fact I did try changing my macro to Button1_Click. But now after copying, the name is changed to a different random value. I've also updated my question with some more information. – duongntbk Jul 01 '17 at 10:44
  • what is response MIME type you are using to download the file. is it "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; – kumar chandraketu Jul 19 '17 at 13:22
  • @kumarchandraketu: it's "application/vnd.ms-excel.sheet.macroEnabled.12". – duongntbk Jul 20 '17 at 00:25
  • I had similar situation for xls template. it fixed after i used correct MIME type and BinaryWrite for response. I'll share my snippet. – kumar chandraketu Jul 20 '17 at 15:56

1 Answers1

0

Please try based on my approach. I am not sure if it will work for you or not. In my requirement, I have read .xltm (xlsx template) file and write it back in .xltm file after manipulation.

try
{
    Response.Clear();    
    Response.ContentType = "application/vnd.ms-excel.template.macroEnabled.12";
    Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", @"Excel_Template_" + DateTime.Now.ToString("yyyy-dd-M-HH-mm") + ".xltm"));

    Response.BinaryWrite(System.IO.File.ReadAllBytes(HostingEnvironment.MapPath("~/Template/manipulated_Excel_Template.xltm")));
    Response.End();
}
catch (Exception Ex)
{
    ...
}

you can find various MIME types supported by MS here for different types of file formats/Extensions.

kumar chandraketu
  • 2,232
  • 2
  • 20
  • 25