OpenXML SDK将VBA注入到Excel工作簿中 [英] OpenXML SDK Inject VBA into excel workbook
问题描述
我可以成功地将一部分VBA代码注入到生成的excel工作簿中,但是我想做的是使用Workbook_Open()事件,以便在文件打开时执行VBA代码.我正在将子项添加到我的xlsm模板文件中的"ThisWorkbook"对象中.然后,我使用openxml生产力工具来反映代码并获取编码的VBA数据.
I can successfully inject a piece of VBA code into a generated excel workbook, but what I am trying to do is use the Workbook_Open() event so the VBA code executes when the file opens. I am adding the sub to the "ThisWorkbook" object in my xlsm template file. I then use the openxml productivity tool to reflect the code and get the encoded VBA data.
生成文件并查看VBA时,看到"ThisWorkbook"和"ThisWorkbook1"对象.我的VBA在"ThisWorkbook"对象中,但是代码永远不会在打开时执行.如果我将VBA代码移动到"ThisWorkbook1"并重新打开文件,则可以正常工作.为什么要创建额外的"ThisWorkbook"?是否无法使用Workbook_Open()子项插入Excel电子表格?这是我正在使用的C#代码的片段:
When the file is generated and I view the VBA, I see "ThisWorkbook" and "ThisWorkbook1" objects. My VBA is in "ThisWorkbook" object but the code never executes on open. If I move my VBA code to "ThisWorkbook1" and re-open the file, it works fine. Why is an extra "ThisWorkbook" created? Is it not possible to inject an excel spreadsheet with a Workbook_Open() sub? Here is a snippet of the C# code I am using:
private string partData = "..."; //base 64 encoded data from reflection code
//open workbook, myWorkbook
VbaProjectPart newPart = myWorkbook.WorkbookPart.AddNewPart<VbaProjectPart>("rId1");
System.IO.Stream data = GetBinaryDataStream(partData);
newPart.FeedData(data);
data.Close();
//save and close workbook
有人有想法吗?
推荐答案
根据我的研究,没有办法以可在C#中操作的格式插入项目零件数据.在OpenXML格式中,VBA项目仍以二进制格式存储.但是,将VbaProjectPart
从一个Excel文档复制到另一个Excel文档应该可以.因此,您必须事先确定要项目部分说些什么.
Based on my research there isn't a way to insert the project part data in a format that you can manipulate in C#. In the OpenXML format, the VBA project is still stored in a binary format. However, copying the VbaProjectPart
from one Excel document into another should work. As a result, you'd have to determine what you wanted the project part to say in advance.
如果您对此表示满意,则可以将以下代码以及相应的宏代码添加到"ThisWorkbook" Microsoft Excel对象中的模板Excel文件中:
If you are OK with this, then you can add the following code to a template Excel file in the 'ThisWorkbook' Microsoft Excel Object, along with the appropriate Macro code:
Private Sub Workbook_Open()
Run "Module1.SomeMacroName()"
End Sub
要将VbaProjectPart
对象从一个文件复制到另一个文件,可以使用如下代码:
To copy the VbaProjectPart
object from one file to the other, you would use code like this:
public static void InsertVbaPart()
{
using(SpreadsheetDocument ssDoc = SpreadsheetDocument.Open("file1.xlsm", false))
{
WorkbookPart wbPart = ssDoc.WorkbookPart;
MemoryStream ms;
CopyStream(ssDoc.WorkbookPart.VbaProjectPart.GetStream(), ms);
using(SpreadsheetDocument ssDoc2 = SpreadsheetDocument.Open("file2.xlsm", true))
{
Stream stream = ssDoc2.WorkbookPart.VbaProjectPart.GetStream();
ms.WriteTo(stream);
}
}
}
public static void CopyStream(Stream input, Stream output)
{
byte[] buffer = new byte[short.MaxValue + 1];
while (true)
{
int read = input.Read(buffer, 0, buffer.Length);
if (read <= 0)
return;
output.Write(buffer, 0, read);
}
}
希望有帮助.
这篇关于OpenXML SDK将VBA注入到Excel工作簿中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!