Get all the VBA macros of a workbook by EPPlus

Question

In the sample Sample15.cs downloaded from EPPlus, I see that EPPlus can write/inject VBA macros. However, I don't see how to read all the VBA macros from a .xlsm workbook.

Can EPPlus interpret VbaProject.bin and support this functionality?

PS: it seems that Open XML SDK cannot do this either (please correct me if I am wrong), that's why I am considering EPPlus...

Answer 1

If you just want to read the code in each modules you just have to go through the 'Workbook.VbaProject.Modules' collection like this:

var fi = new FileInfo(@"C:\temp\Book1.xlsm");
using (var pck = new ExcelPackage(fi))
{
    var modules = pck.Workbook.VbaProject.Modules;
    foreach (var module in modules)
    {
        Console.WriteLine($"Module Name: {module.Name}{Environment.NewLine}Code:");
        Console.Write(module.Code);
        Console.WriteLine("---------");
    }

Which will give you this in the output (I just created an excel xlsm with two modules):

Module Name: ThisWorkbook
Code:
---------
Module Name: Sheet1
Code:
---------
Module Name: Module1
Code:
Public Sub proc1()
    Dim wb As Workbook
    Set wb = ActiveWorkbook

    Dim ws As Worksheet
    Set ws = wb.ActiveSheet

    ws.Cells(1, 1).Value = "proc1"

End Sub

---------
Module Name: Module2
Code:
Public Sub proc2()
    Dim wb As Workbook
    Set wb = ActiveWorkbook

    Dim ws As Worksheet
    Set ws = wb.ActiveSheet

    ws.Cells(2, 1).Value = "proc2"

End Sub

---------