如何将VBA代码注入Excel .xlsm而不使用Interop？ [英] How to inject VBA code into Excel .xlsm without using Interop?

问题描述

我的目标是将宏添加到Excel工作簿中，而无需在Excel信任中心中启用对VBA项目对象模块的信任访问。 （启用访问似乎是一个安全风险）。

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).

在我的初始实际查找搜索中找到随机的拼图：
- 我看到VBA脚本存储在将.xlsm文件压缩为vbaProject.bin。
- 有几个可以使用excel文件的免费/商业资源：
创建没有interop的excel和带有或不带行列列表的模板

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

这将是不错的在C＃项目中只需要一个VBA脚本的文件，C＃代码从VBA互操作中提取并注入到Excel文档中。任何快速/快速/简单/直接的方式来做到这一点，还是应该用与上述相关的免费/商业资源来玩？

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?

推荐答案

p>使用 OpenXML SDK 2.0 ：

1. 创建您的宏代码并将其保存为.xlsm格式，如snorehorse.xlsm。


2. 打开snorehorse.xlsm OpenXML生产力工具包，并在树根上执行反映代码。


3. 查找宏的二进制代码。它是一种字符串格式，看起来像随机字符。


4. 在IDE中，添加对OpenXML SDK的引用，并以编程方式创建或打开要将宏代码注入的excel文件。


5. 将步骤3中找到的宏字符串复制到代码中。


6. 将新的vba部分添加到目标。


7. 将字符串数据输入新的vba部分。


8. 保存并运行，并高兴您绕过信任中心。

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.

示例代码：

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));
    }

我选择将OpenXML SDK dll添加到项目的本地构建中，以便结束用户不需要自己安装SDK。

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.

我认为这可以在较低级别上使用XML，而不使用OpenXML SDK，但是没有尝试学习如何做到这一点。如果有人可以发布代码，我会接受我的答案。

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.

另外，如果有一个编程方式来转换VBA脚本，在嵌入式资源文件中，成为excel期望的格式的二进制字符串，可以绕过每次要更改宏代码时必须复制并粘贴新的二进制数据串。这将是对我的一个很好的答案。

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.

谢谢。

这篇关于如何将VBA代码注入Excel .xlsm而不使用Interop？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！