CODEWITHSUNDEEP
vb.netexcelsystem.io.packagingvba
Maury Markowitz
Maury Markowitz

Reputation: 9279

Adding a macro file to an XLSX/XLSM file using Packaging

I'm using System.IO.Packaging to build simple Excel files. One of our customers would like to have an autorun macro that updates data and recalcs the sheet.

Pulling apart existing sheets I can see that all you need to do is add the vbaProject.bin file and change a few types in the _rels. So I made the macro in one file, extracted the vbaProject.bin, copied it into another file, and presto, there's the macro.

I know how to add package parts when they are in XML format, like the sheets or the workbook itself, but I've never added a binary file and I can't figure it out. Has anyone done this before?

Upvotes: 1

Views: 1303

Answers (1)

Maury Markowitz
Maury Markowitz

Reputation: 9279

Ok I got it. Following TnTinMn's suggestion:

  1. Open a new workbook and type in your macro. Change the extension to zip, open it, open the xl folder and copy out the vbaProject.bin to somewhere easy to find.

  2. In your .Net code, make a new Part and add it to the Package as 'xl/vbaProject.bin'. Copy over byte-for-byte from the vbaProject.bin you extracted above. It will be compressed as you add the bytes.

  3. Then you have to add a relationship to the workbook that points to your new file. You can find those relationships in xl/_rels/workbook.xml.rels.

  4. You also have to add a content type entry at the root of the document, which goes into the [Content Types].xls. This happens automatically when you use the ContentType parameter of CreatePart

  5. And finally, change the extension to .xlsm or .xltm

I'm extracting the following from many places in my code, so this is pseudo...

'the package...
Dim xlPackage As Package = Package.Open(WBStream, FileMode.Create)
'start with the workbook, we need the object before we physically insert it
Dim xlPartUri As URI = PackUriHelper.CreatePartUri(New Uri(GetAbsoluteTargetUri("/", "xl/workbook.xml"), UriKind.Relative)) 'the URI is relative to the outermost part of the package, /
Dim xlPart As PackagePart = xlPackage.CreatePart(xlPartUri, "application/vnd.ms-excel.sheet.macroEnabled.main+xml", CompressionOption.Normal)
'add an entry in the root _rels folder pointing to the workbook
xlPackage.CreateRelationship(xlPartUri, TargetMode.Internal, "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument", "xlWorkbook") 'it turns out the ID can be anything unique
'now that we have the WB part, we can make our macro relative to it
Dim xlMacroUri As URI = PackUriHelper.CreatePartUri(New Uri(GetAbsoluteTargetUri("/xl/workbook.xml", "vbaProject.bin"), UriKind.Relative))
Dim xlMacroPart as PackagePart = xlPackage.CreatePart(xlPartUri, "application/vnd.ms-office.vbaProject", CompressionOption.Normal)
'time we link the vba to the workbook
xlParentPart.CreateRelationship(xlPartUri, TargetMode.Internal, "http://schemas.microsoft.com/office/2006/relationships/vbaProject", "rIdMacro") 'the ID on the macro can be anything as well
'copy over the data from the macro file
Using MacroStream As New FileStream("C:\yourdirectory\vbaProject.bin", FileMode.Open, FileAccess.Read)
    MacroStream.CopyTo(xlMacroPart.GetStream())
End Using
'
'now write data into the main workbook any way you like, likely using new Parts to add Sheets

Upvotes: 1

Related Questions