OpenXML SDK Inject VBA into excel workbook

Question

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.

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

Anyone have ideas?

Within the VBA editor, when you right-click on ThisWorkbook and ThisWorkbook1, does either have the Remove ThisWorkbook... selection grayed out? — Derek B. Bell
– Derek B. Bell, Commented Mar 21, 2012 at 14:03

Cellcon · Accepted Answer · 2017-11-08 15:40:45Z

6

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.

edited Nov 8, 2017 at 15:40

Cellcon

1,2952 gold badges12 silver badges30 bronze badges

answered Jul 17, 2012 at 16:04

Lentonator

861 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lentonator Over a year ago

I should also add that after writing I came across a platform called ClosedXML. It's designed specifically for interacting with OpenXML Excel files and is very intuitive, it may also help.

David Rogers · Accepted Answer · 2018-06-14 15:08:53Z

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

David Rogers · Accepted Answer · 2018-06-14 01:37:56Z

0

You need to specify "codeName" attribute in the "xl/workbook..xml" object After 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.

edited Jun 14, 2018 at 1:37

David Rogers

2,7435 gold badges49 silver badges95 bronze badges

answered May 16, 2018 at 11:32

ZlotaMoneta

1

Collectives™ on Stack Overflow

OpenXML SDK Inject VBA into excel workbook

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related