CustomUI.xml (Ribbon UI) injection

[mattpalermo]

The CustomUI.xml is another component of the workbook that is very well suited to being stored as a text file instead of being bundled into the workbook binary. To achieve this I can think of two different solutions:

1. Use a tool like EffOff in the build process to inject the CustomUI.xml every time the workbook is built.
2. Unpack the workbook into a decompressed stack of XML files. The CustomUI.xml will be in there somewhere. Then as part of the build process pack the workbook, and then when it comes to committing changes, unpack the workbook.

This feature can be used to implement issue #54.

[mattpalermo]

As a reference, this is the code that EffOff uses to achieve CustomUI injection:

            string CustomUIContent = File.ReadAllText(config.CustomUI);

            SpreadsheetDocument document = SpreadsheetDocument.Open(opts.target, true);
            RibbonAndBackstageCustomizationsPart part = document.RibbonAndBackstageCustomizationsPart;
            if (part == null)
            {
                part = document.AddRibbonAndBackstageCustomizationsPart();
            }
            part.CustomUI = new CustomUI(CustomUIContent);
            part.CustomUI.Save();
            document.Close();

[mattpalermo]

Perhaps this idea should be canned in favor of the method presented in PR #59

[byronwall]

One way to do this: https://github.com/byronwall/bUTL/blob/master/scripts/BuildFile.bas

I've been working on an Excel utility add-in that includes a "build from source" option to help with getting source back into git. It roughly duplicates your efforts here but includes the steps to export the Ribbon definition since it's a core part of the add-in. I took the 2nd approach listed above: extract the xlam file and dump those files somewhere. I put the files into a folder called src/package which is then checked into git. The downside of keeping all the files is that they are user/workstation specific, so you get a number of extra diffs that aren't intended. I could probably only keep the CustomUI file, but I haven't pursued that. (The same thing happens with frx files which may not have actually changed).

There is also a script in that folder which handles the rebuilding step. It simply repackages the package folder, opens the add-in, deletes all the existing VBA code, and then reimports the new modules from source. It could be improved, but has worked so far.

I call the Excel macros from PowerShell to give a simple command line interface instead of having to open the add-in exporter in Excel.

[spences10]

Awesome @byronwall! do you have to call it from PS?

[byronwall]

No, you can make the calls from anywhere. That source code file is pulled from the VBA in build manager.xlsm. The paths in that workbook/macro are currently relative to how the repo is setup, but those could be easily changed or made variable. Look for the spots where it says butl.xlam, temp.zip, src/..., etc. That code handles both the import and export of the source code and Excel file unzipping/repackaging.

The PowerShell part of it was to avoid opening Excel to run the macros. If you're looking to integrate this into an existing Excel add-in, obviously having to open Excel is not a concern. The PowerShell file is simply starting an instance of Excel and running the macro behind the scenes. You need Excel running somewhere to get working access to the VBE commands to export the source code. You don't need VBE/Excel to unzip the Excel file or repackage it; just to get the source code extracted.