OpenXML SDK Inject VBA into excel workbook
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.
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
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); }}
Hope that helps.
I found that the other answers still resulted in the duplicate "Worksheet" object. I used a similar solution to what @ZlotaMoneta said, but with a different syntax found here:
List<VbaProjectPart> newParts = new List<VbaProjectPart>();using (var originalDocument = SpreadsheetDocument.Open("file1.xlsm"), false)){ newParts = originalDocument.WorkbookPart.GetPartsOfType<VbaProjectPart>().ToList(); using (var document = SpreadsheetDocument.Open("file2.xlsm", true)) { document.WorkbookPart.DeleteParts(document.WorkbookPart.GetPartsOfType<VbaProjectPart>()); foreach (var part in newParts) { VbaProjectPart vbaProjectPart = document.WorkbookPart.AddNewPart<VbaProjectPart>(); using (Stream data = part.GetStream()) { vbaProjectPart.FeedData(data); } } //Note this prevents the duplicate worksheet issue spreadsheetDocument.WorkbookPart.Workbook.WorkbookProperties.CodeName = "ThisWorkbook"; }}
You need to specify "codeName" attribute in the "xl/workbook..xml" objectAfter feeding the VbaProjectPart with macro. Add this code:
var workbookPr = spreadsheetDocument.WorkbookPart.Workbook.Descendants<WorkbookProperties>().FirstOrDefault();workbookPr.CodeName = "ThisWorkBook";
After opening the file everything should work now.
So, to add macro you need to:
Change document type to macro enabled
Add VbaProjectPart and feed it with earlier created macro
Add workbookPr codeName attr in xl/workbook..xml with value "ThisWorkBook"
Save as with .xlsm ext.