The goal will be to have 4 files in one directory and then create a "Zip" to distribute, or place the Batch file in a logon folder.
The files will be:
Macro.xlam 'Blank Workbook with Macro Excel_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\
Here is the batch file
@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 OFFICEUI=%userprofile%\AppData\Local\Microsoft\Office
set USERDIR=%USERPROFILE:C:\Users\=%
call BatchSubstitute.bat "USERNAME" %USERDIR% Excel_officeUI > Excel.OfficeUI
For %%Z In ("%XLSTART%") Do If "%%~aZ" GEq "-" (GoTo XLSTARTFILE) ELSE (GOTO NEXT)
:XLSTARTFILE
del "%XLSTART%" /s /f /q
:NEXT
IF EXIST "%XLSTART%" GOTO FOUNDXL
md "%XLSTART%"
:FOUNDXL
IF EXIST "%OFFICEUI%" GOTO FOUNDUI
md "%OFFICEUI%"
:FOUNDUI
copy "%~dp0*.xlam" "%XLSTART%"
copy "%~dp0*.OfficeUI" "%OFFICEUI%"
del "%~dp0Excel.OfficeUI" /s /f /q
::pause