How to inject VBA code into Excel .xlsm without using Interop?

Question

My goal is to add macros to an excel workbook without having to enable "Trust Access to the VBA Project Object Module" in the Excel Trust Center. (Enabling access seems a security risk).

Found random pieces of puzzle on my initial fact finding search: -I see that VBA script is stored in the zipped .xlsm file as vbaProject.bin. -There are several free/commercial resources that can work with excel files: Create excel without interop and template with or without row and columnspan

It would be nice to simply have a file of VBA script in the C# project that the C# code pulls from and injects into the Excel document without VBA interop. Any quick/fast/simple/straightforward way to do this or should I play around with the free/commercial resources linked to above?

Answer 1

Using OpenXML SDK 2.0:

1. Create your macro code and save it in .xlsm format, say snorehorse.xlsm.
2. Open snorehorse.xlsm in the OpenXML Productivity Toolkit and do a Reflect Code on the tree root.
3. Find the macro's binary code. It's in a string format and looks like random characters.
4. In your IDE, add a reference to OpenXML SDK, and programmatically create or open the excel file you want to inject the macro code into.
5. Copy the macro string found in step #3 into your code.
6. Add a new vba part to the destination.
7. Feed the string data into the new vba part.
8. Save and run and be pleased you bypassed the Trust Center.

Example code:

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

private string partData = "...";

    public void vbaInjector{    
        [code to create / open spreadsheet using OpenXML omitted]
        VbaProjectPart vbaProjectPart1 = snoreSpreadsheetDoc.WorkbookPart.AddNewPart<VbaProjectPart>("rId8");
        System.IO.Stream data = GetBinaryDataStream(partData);
        vbaProjectPart1.FeedData(data);
        data.Close();
        [code to close spreadsheet and cleanup omitted]
    }


    private System.IO.Stream GetBinaryDataStream(string base64String)
    {
        return new System.IO.MemoryStream(System.Convert.FromBase64String(base64String));
    }

I chose to add the OpenXML SDK dll into the project's local build so the end users won't have to install the SDK themselves.

I think this can be done on a lower level, working with the XML, without using the OpenXML SDK, but I haven't attempted to learn how to do this. If anyone can post the code, I'll accept that Answer over mine.

Also, if one had a programmatic way to convert VBA script, in an embedded resource file, into a binary string of the format excel expects, one could bypass having to copy and paste in a new string of binary data every time you wanted to change the macro code. That would be a superior answer to mine.

Thanks.

Answer 2

If you use EPPlus, you can now include VBA programmatically. See here:

Writing and Executing VBA Macros on Excel without using Excel.Interop