Search code examples
vbaexcelinstallationadd-in

How do I automatically add custom Macro Add-in to toolbar/ribbon after adding it in?

How do I make adding in a custom Macro XLAM file as quick and simple as possible?

I have a macro saved as a XLAM file. I want other non-Excel-savvy people I work to be able to download the XLAM file, add it in like you would with any other add-in (e.g. Solver) and have it be a simple fool-proof process.

However, currently it involves adding it in, then going to the file > options > customize ribbon > Macros > MyFancyMacroAddin. Then clicking on the add in, then creating a new tab and sub group, to which the add-in can me moved from the left column to the right column. This is a long, error-prone process. Whenever I add in something like Solver, it just pops up in my toolbar after adding it in. However, it requires more steps it seems when doing a custom XLAM.

Any input would be greatly appreciated.

Solution

  • Microsoft Excel Ribbon

    To create create/edit the ribbon in Excel use Custom UI Editor Tool. Here are some sources for creating the XML:

    To Install an Excel VBA Add-In

    I use XCOPY in a .bat file for installing my .xlam files to user machines. I'll setup a folder on a network share e.g. S:\Addins\MyFancyMacroAddin and put the .xlam and .bat file in it. Then I distribute a shortcut to the .bat file to the end-users. It copies the file from the current directory to the XLSTART folder on their machine.

    You can click on the animation below to view it a bit easier.

    Example:

    example

    Code:

    @ECHO OFF

REM |--------------------------------------------------------------------------------------------------------------------
REM | Purpose:  Generic Excel Addin Install
REM |--------------------------------------------------------------------------------------------------------------------


REM
REM     /E   = Copies directories and sub-directories, including empty ones. Same as /S /E. May be used to modify /T. 
REM     /D:m-d-y = Copies files changed on or after the specified date. 
REM        If no date is given, copies only those files whose source time is newer than the destination time. 
REM     /K   = Copies attributes. Normal Xcopy will reset read-only attributes. 
REM     /Q   = Does not display file names while copying. 
REM     /R   = Overwrites read-only files. 
REM     /Y   = Suppresses prompting to confirm you want to overwrite an existing destination file. 
REM

REM Copy the install directory and sub-directories
REM echo f | XCOPY ".\MyFancyMacroAddin.xlam" "%AppData%\Microsoft\AddIns\MyFancyMacroAddin.xlam" /E /K /Q /R /Y /D
    echo f | XCOPY ".\MyFancyMacroAddin.xlam" "%AppData%\Microsoft\Excel\XLSTART\MyFancyMacroAddin.xlam" /E /K /Q /R /Y /D
REM echo f | XCOPY ".\MyFancyMacroAddin.xlam" "%AppData%\Roaming\Microsoft\Excel\XLSTART\MyFancyMacroAddin.xlam" /E /K /Q /R /Y /D