The process for different Office Applications varies, but I have used this for Word and Excel so far.
The main points are as follows:
- Excel Macros get distributed as "XLAM" add-ins to XLStart and run via customized "Excel.OfficeUI", this requires modifying usernames in the paths via "batchsubstitute.bat"
- Word Macros get distibuted as "DOTM" which are macro enabled templates inside "STARTUP" and run via customized "Word.OfficeUI", this doesn't require custom paths
The files will be:
Macro.dotm 'Word Template w/ Macro Macro.xlam 'Blank Workbook with Macro Excel_officeUI 'Customized Ribbon with button to run Macro Word.OfficeUI 'Customized Ribbon with button to run Macro Setup.cmd 'Will Copy Files to needed location BatchSubstitute.bat 'Used to replace Hard Coded USERNAME w/ current users %USERNAME%
Macro.xlam: (https://trumpexcel.com/excel-add-in/#Save-and-Install-the-Add-in)
- Note: If you want to change name from book1 do it now, before inserting module
- First open a new Excel Workbook and open VBA Editor then Insert Module
- Now save book as "Excel Add-In" (*.xlam)
- Note: Save it to XLSTART folder→ Eg: %AppData%\Microsoft\Excel\ (You may need to create XLSTART Folder)
- Close and start excel, this AddIn should load, check VBA Editor. You must launch the AddIn via XLSTART before Customizing Ribbon!
- Don't close workbook, just move on to next step!
Excel.officeUI: (https://www.dummies.com/software/microsoft-office/excel/how-to-assign-macros-to-the-ribbon-and-the-quick-access-toolbar-in-excel-2013/)
- Create the Custom Ribbon Button by going to File → Options → Customize Ribbon
- Click Macros from the "Choose Commands From" drop down list
- On the right under "Customize the Ribbon", Under "Home" select "New Group"
- Name it "Macros" by right clicking "New Group (Custom)" → Rename
- Select Macro from left side and hit "Add"
- You should see custom ribbon option now!
- Select OK
- Exit workbook, no need to save, this should have been a blank workbook!
Test! → Open a relevant workbook and test Custom Ribbon Option!
Setup.cmd:
Your local machine is now setup, but we want to make this portable and deploy to other computers!
Cut your "Macro.xlam" & "Excel.OfficeUI" now to your project dir and then create batch script to deploy!
(I like to cut the files, then open Excel, see that the Ribbon is gone, then test w/ Deploy Script and watch it come back!)
- NOTES: Excel.OfficeUI has hardcoded "USERNAME" in the Path Dir. The best solution I found was to use a separate batch file to Find/Replace USERNAME with current USERNAME variable.
- Find BatchSubtitute.bat here → https://www.dostips.com/?t=batch.findandreplace
- Rename Excel.OfficeUI to Excel_OfficeUI
- Inside Excel_OfficeUI Replace any instances of "LoginUserName" with "USERNAME"
- Call Bat to Find and Replace "USERNAME" with real Username, this makes it "Portable"
Macro.xlam --> %AppData%\Microsoft\AddIns\ Excel.OfficeUI --> %userprofile%\AppData\Local\Microsoft\Office\
Batch Files
Deploy_Office_AddIns.cmd
@echo off pushd %~dp0 cls ::FreeSoftwareServers.com ::https://www.freesoftwareservers.com/display/FREES/Distributing+Macro+via+Add-In+-+Customized+Ribbon+-+Via+Batch set XLSTART=%AppData%\Microsoft\Excel\XLSTART set WORDSTARTUP=%AppData%\Microsoft\Word\STARTUP set OFFICEUI=%userprofile%\AppData\Local\Microsoft\Office set USERDIR=%USERPROFILE:C:\Users\=% call "%~dp0\Remove_Office_AddIns.cmd" call "%~dp0\Excel\BatchSubstitute.bat" "USERNAME" %USERDIR% "%~dp0\Excel\Excel_officeUI" > "%~dp0\Excel\Excel.OfficeUI" IF EXIST "%XLSTART%" GOTO FOUNDXL md "%XLSTART%" :FOUNDXL IF EXIST "%WORDSTARTUP%" GOTO FOUNDWORDSTARTUP md "%WORDSTARTUP%" :FOUNDWORDSTARTUP IF EXIST "%OFFICEUI%" GOTO FOUNDUI md "%OFFICEUI%" :FOUNDUI ::Excel copy "%~dp0\Excel\*.xlam" "%XLSTART%" copy "%~dp0\Excel\*.OfficeUI" "%OFFICEUI%" del "%~dp0\Excel\Excel.OfficeUI" /s /f /q ::Word copy "%~dp0\Word\*.dotm" "%WORDSTARTUP%" copy "%~dp0\Word\*.OfficeUI" "%OFFICEUI%" ::pause
Remove_Office_AddIns.cmd
@echo off
pushd %~dp0
cls
::FreeSoftwareServers.com
::https://www.freesoftwareservers.com/display/FREES/Distributing+Macro+via+Add-In+-+Customized+Ribbon+-+Via+Batch
set XLSTART=%AppData%\Microsoft\Excel\XLSTART
set WORDSTARTUP=%AppData%\Microsoft\Word\STARTUP
set OFFICEUI=%userprofile%\AppData\Local\Microsoft\Office
set USERDIR=%USERPROFILE:C:\Users\=%
For %%Z In ("%XLSTART%") Do If "%%~aZ" GEq "-" (GoTo XLSTARTFILE) ELSE (GOTO NEXT)
:XLSTARTFILE
del "%XLSTART%" /s /f /q
For %%Z In ("%WORDSTARTUP%") Do If "%%~aZ" GEq "-" (GoTo WORDSTARTUPFILE) ELSE (GOTO NEXT)
:WORDSTARTUPFILE
del "%WORDSTARTUP%" /s /f /q
:NEXT
::pause