Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

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: (

  • 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: (

  • 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!


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 →
  • 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

set XLSTART=%AppData%\Microsoft\Excel\XLSTART
set OFFICEUI=%userprofile%\AppData\Local\Microsoft\Office

call BatchSubstitute.bat "USERNAME" %USERNAME% Excel_officeUI > Excel.OfficeUI

md "%XLSTART%"

copy "%~dp0*.xlam" "%XLSTART%"
copy "%~dp0*.OfficeUI" "%OFFICEUI%"

del "%~dp0*.OfficeUI" /s /f /q
  • No labels