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