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:
- 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!
- 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 → 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"
Here is the batch file