Search code examples
excelsharepointribbonvba

Sharing custom macros and custom Excel Ribbon with others via Sharepoint

My colleagues and I use Excel 2013 and Sharepoint

I have written some custom macros which I have stored in Personal.xlsb I have also customized the ribbon to add a tab to access these macros.

What is the best way of sharing these macros and the ribbon with my colleagues? (and keeping them updated)

One way is as follows (and this needs to be done each time there is a change)

  1. right-click Ribbon | Customize the Ribbon | Import/Export | Export all Customizations and send this XML file to my colleagues and ask them to import it
  2. Send my colleagues my Personal.xlsb and ask them to overwrite their own with this.

However is there a better way - involving manual effort on the part of my colleagues as little as possible.

I thought perhaps of saving the macros as a sharepoint document, and then editing the XML document to point to this sharepoint document.

Solution

  • The best answer that I have come up with is as follows:

    1. Not to save the files in personal.xlb but to save them in an AddIn (an .xlam file)
    2. To add the menus to the Addin via Custom UI Editor For Microsoft Office (downloadable from http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/08/07/7293.aspx)
    3. To save the file in the "Shared With Everyone" folder of "SkyDrive Pro"
    4. To send a link to the file to my colleagues

    This seems to solve all the issues. If I do an update then it seems that if my colleagues restart their computer then the addin is updated automatically.