none
How to execute/call macro in .xlsm file using Office Open XML SDK 2.0 RRS feed

  • Question

  • I would like to know how OpenXML API can be used to run macros with .xlsm files? I'm using Open XML API in my project and using "using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(excelPath, true))" to open the excel file and then inserting the values in excel file using Open XML API's, through a windows service.

    ISSUE -  After inserting the values, we are uploading the .xlsm file to a SharePoint library. When user/client opens the file, .xlsm file opens and We are running a macro (implementing formulas) on open event of the excel. However, when user open in Office 2010, they don't see their data populated through macro but when they open in Office Excel 2013, the macro runs properly, and data is also populated.

    Question - I think this is the issue related to "How we are calling MACRO". Can we call our macro through .NET C# Code in OPEN XML API instead of calling on open event of excel file itself..? Through this way, I think, we will also be able to present .xlsx file to our client (as client needs .xlsx file and not .xlsm).

    Please suggest !!!


    Vipul Jain

    Monday, September 22, 2014 7:06 AM

All replies

  • Hi,

    >> I think this is the issue related to "How we are calling MACRO". Can we call our macro through .NET C# Code in OPEN XML API instead of calling on open event of excel file itself..? <<

    According to your description, you are using Open XML SDK to create an *.xlsm file and uploading the file into SharePoint library. Then the user find the workbook open event isn't triggered when opening the workbook in Office 2010. But it works well in Office 2013.

    If I understand correctly, I don't think the issue is related to Open XML SDK and "How we are calling MACRO". In Excel, if we create a workbook with Workbook.Open event in the vba macro, the open event can run automatically when opening the workbook, so we don't need to call it in Open XML SDK. And Open XML SDK is used to create or interact all Open XML format Office documents, it cannot be used to call any macro in spreadsheet.

    Since you said the open event works well in Office 2013, I think the open event may be defined correctly. But you can check it. In addition, I suggest you checking whether the macros are disabled in Office 2010 as followed when opening. In fact, when you open a workbook which comes from Web, Excel will disable the macros with notification. Ater clicking the Enable Content button, the open event macro will run.

    If you enable all Macros in File->Option->Trust Center->Trust Center Settings->Macro Settings, the macros won't be disabled. But we don't recommand you to enable all macros, because potentially dangerous can run.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 23, 2014 6:51 AM
    Moderator
  • Hello,

    First of all, we are not using Open XML SDK to create .xlsm file. We have a template which is macro-enabled, we download that to local drive using code, inserts values using Open XML API's and then again uploads that template to SharePoint library.

    Correctly understood, then user clicks on file in SP Library, where we have written a macro which triggers on OPEN event of excel file. Users can see the data in Office Excel 2013, but not in Excel 2010, because macro doesn't runs properly while opening in Excel 2010.

    We already tried enabling the macros in excel 2010 (File->Option->Trust Center->Trust Center Settings->Macro Settings), but that doesn't solves our issue. :(..Also, if we enable macros in excel 2010 by default, in each excel file on that system, the macros get enables (which is potentially dangerous) , as I think it's a OS setting and client will never allow to enable macros by default.

    Please suggest any other alternative, if possible..!!!


    Vipul Jain

    Tuesday, September 23, 2014 5:18 PM
  • Hi Vipul Jain

    >> I would like to know how OpenXML API can be used to run macros with .xlsm files?

    OpenXML SDK is used to manipulate the OpenXML Format Office documents and I’m afraid it did not provide an engine to run macros.

    According to your description, the open event fired on Office 2013 but it did not fire on Office 2010. If so, I suspect this issue is related to a specific spreadsheet. Would you mind sharing a sample spreadsheet through OneDrive to help us reproduce this issue?

    Regards,

    Jeffrey


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 24, 2014 3:09 AM
    Moderator