OpenXML SDK Inject VBA into Excel Workbook
I can successfully insert a piece of VBA code into the excel workbook I have created, but what I am trying to do is use the Workbook_Open () event to have the VBA code execute when the file is opened. I am adding sub to the "ThisWorkbook" object in my xlsm template file. I then use the openxml performance tool to flip the code and get the VBA encoded data.
When the file is generated and I browse the VBA, I see the "ThisWorkbook" and "ThisWorkbook1" objects. My VBA is in the "ThisWorkbook" object, but the code never runs when opened. If I translate my VBA code to "ThisWorkbook1" and reopen the file, it works fine. Why was the extra "ThisWorkbook" added? Can't insert Excel spreadsheet using Workbook_Open ()? Here is the C # code snippet I'm 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
Does anyone have any idea?
source to share
Based on my research, there is no way to insert project detail data in a format that can be manipulated in C #. In OpenXML format, the VBA project is still saved in binary format. However, copying VbaProjectPart
from one Excel document to another should work. As a result, you will need to define what you wanted the part of the project to say beforehand.
If you are ok with this, then you can add the following code to the Excel Excel file in the "Microsoft Excel" of this "ThisWorkbook" along with the corresponding macro code:
Private Sub Workbook_Open()
Run "Module1.SomeMacroName()"
End Sub
To copy an object VbaProjectPart
from one file to another, you must use the following code:
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);
}
}
Hope it helps.
source to share