6

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?

Community
  • 1
  • 1
Snorex
  • 904
  • 12
  • 29

2 Answers2

6

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.

Kevin Dimey
  • 709
  • 6
  • 15
Snorex
  • 904
  • 12
  • 29
  • After 1 hour i still can't inject any code into my project without corrupting it. Only works if i execute ALL the code reflected by the OpenXML Toolkit. – Marcello Grechi Lins Feb 14 '14 at 17:43
  • Shot in the dark... maybe "rId8" is already used? How about change it to something like "rId888"? – Snorex Aug 27 '14 at 14:43
2

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

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

Community
  • 1
  • 1
Doug WB
  • 340
  • 6
  • 14