2

Scope:

I am generating automated reports using excel, but since we have restrictions on our server, we can't install Excel.Interop or use any COM object to perform such action.

We have been using EPPLUS as our main helper on this task but it has some serious restrictions when it comes to more intricated things such as PivotCharts and Macros.

We have just finished the project, missing only the PivotCharts since EPPLUS has no support for them.

Question:

How can we :

A) Write Macros to a .xlsm file? (We could write one named "Auto_Open" so that excel would run it uppon opening, creating the charts and stuff)

OR

B) Run a macro within our code to generate those charts after putting all the needed data there?

Not using Excel.Interop is a must at this moment. We are open for some suggestions such as SpreadsheetGear and EasyXLS, but we can't find any sample or piece of code that actually shows how to Write or Run macros.

Thanks in advance

Marcello Grechi Lins
  • 3,350
  • 8
  • 38
  • 72

3 Answers3

17

I realise this question is a bit old, but in the interest of helping future visitors I'll add that the current version of EPPlus (4.0.4 at time of writing) supports the inclusion of VBA.

Where excelDoc is your ExcelPackage, you can drop it in using something along the lines of:

excelDoc.Workbook.CreateVBAProject();

StringBuilder vbaCode = new StringBuilder();

vbaCode.AppendLine("Private Sub Workbook_Open()");
vbaCode.AppendLine("    Application.DisplayFormulaBar = False");
vbaCode.AppendLine("End Sub");

excelDoc.Workbook.CodeModule.Code = vbaCode.ToString();

Hope this helps.

Doug WB
  • 340
  • 6
  • 14
  • 2
    How do you actually execute the vba code once you have put it in the workbook? – BeYourOwnGod Apr 25 '17 at 07:09
  • 1
    @VanceSmith In the above example, because the code is within the Workbook_Open subroutine, it will execute automatically upon opening the workbook. – Doug WB Apr 25 '17 at 09:14
  • 1
    that is until the macros are blocked due to an over zealous security area which defaults all settings and blocks macros....so, no workbook_open events will fire – Gary Nov 01 '17 at 21:10
1
  1. Open Excel
  2. Press Alt+F11

You will now be in the VBA IDE.

To run something on Auto Open:

  1. Select ThisWorkbook From the VBAProject in Project Explorer on the Left of your screen

  2. There are Two drop downs near the top of the window one will say (General) and The other (Declarations) click on (General) and select workbook

Excel should automatically bring you to the Open declaration.

Anything written in this sub will execute on open.

user2140261
  • 7,855
  • 7
  • 32
  • 45
  • Thanks for the response. I need the report generator to be fully automated, with no human output. Also, EPPLUS has no support for opening xlsm (macro enabled) files, so there's no way i can do this by hand. – Marcello Grechi Lins Feb 14 '14 at 18:22
  • 1
    @MarcelloGrechiLins Then you might have to look inot using the (Open XML SDK)[http://msdn.microsoft.com/en-us/library/office/bb448854.aspx] – user2140261 Feb 14 '14 at 19:27
  • The OP asks how to add macros without Excel Interop so I assume they don't have the luxury of manually adding a macro. – ProfK Nov 18 '19 at 14:44
1

Rather than attempting to programatically write macros to an xlsm file, why don't you create a template that already contains the macros you want. Then generate your reports from this template.

When a user opens the report (and allows macros to run), then your macro will run and do the manipultations you want (your option A). Your Auto_Open macro could check some suitable condition (e.g. presence of a value in a specific location, perhaps on a VeryHidden sheet so the user can't easily interfere with it) before doing any work, so that it doesn't run when you open an "empty" template.

As for your option B (run the macro before providing the report to the user): this isn't feasible without Excel Automation.

Joe
  • 122,218
  • 32
  • 205
  • 338
  • 1
    My bad, i forgot to mention that EPPLUS does not open xlsm files, so there's no way we can setup a template and work from there. That was our first approach, but it does not worked. – Marcello Grechi Lins Feb 15 '14 at 10:57