Transferring Ribbon Customizations

Some of this information may be out of date.

When I use the "Export" button on my Excel 365 it seems to create a file which contains both my ribbon customisations and the Quick Access Toolbar customisations. The filename was Excel Customisations.exportedUI_20220823.exportedUI (I added the date). There was no sign of a file with the extension .qat.
I did not have time to check in depth, but the file created is not affected by which of the buttons "Customize Ribbon" or "Quick Access Toolbar" (Figure 1 above, left hand pane) is selected. BTW, the exported file is a text file using XML, so it can easily be inspected.

@Srikanth Jayaraman

If you associate the file with a text editor (Notepad or Notepad++), you can edit the file paths. However, it stores them in two formats, so you'll need to change both.

One is the usual path format:
C:\Users\XXXXX\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.xlsb
The second format replaces a backslash with an underscore:
C:_Users_XXXXX_AppData_Roaming_Microsoft_Excel_XLSTART_PERSONAL.xlsb

Please note, the underscore also replaces the ! between the workbook name and macro name.

I've attempted to make these replacements with varying success. Still waiting for someone to make this a little more simple.

Good luck

Hello All, I recently swapped my laptop that the new laptop has a later version of Windows. Most of my macros were stored in personal.xlsb is stored in the folder, c:\users\[user ID]\AppData\Roaming\Microsoft\Excel\XLSTART\Personal.xlsb on my old machine. In the new machine, this path is now changed because the personal data is now stored based on e-mail ID and so the Personal.xlsb is stored in the following address: C:\users\[e-mail address]\AppData\Roaming\Microsoft\Excel\XLSTART\Personal.xlsb
So when I export-import the toolbar from the old computer to the new computer and run the macro on the new machine, I get the (see Figure 1 below) you see here.
There should be some way to edit the path. Are you guys aware of this.
Otherwise, I will recreate each and every Icon on the toolbar.

Is there a way I can export just one of the tabs in the ribbon from Excel 2007 into Excel 2010?

@Michael (Micky) Avidan

I would agree that using Freeze Panes is a possible solutioin to my second problem - but not always.

I have worksheets where Freeze Panes would work perfectly to keep a macro button visible, but I also have other worksheet where it wouldn't be a very desirable solution. On those sheets, the cell(s) above and to the left of the freeze boundary contains important headings or logos. While I suppose it would be possible to use the logo as a button, it would not allow me the ability to put instruction text on the button. On the worksheet I on which I have placed a button to trigger a macro, I have inluded text "Click to clear data" (the macro clears data from a range of cells), and I have also included a note about the keyboard short cut which will also run the macro.

Going back the the original topic of this thread, I have extensively modified my QAT in an attemtp to partially make up for the absence of proper tool bars. The lack of proper, context sensitive toolbars is on of the reasons I sometimes us either Lotus Smart Suite (I have the last version) or Open Office, for spreadsheet or workproccing.

@John Mann,
Your second "problem" is not a problem at all.
Try to add 3-4 empty rows at the top of the sheet and place the commndbutton in this empty area.
Now, all you need is to Pane Freeze those 3-4 rows (and some columns) in order to have the Button available all the time.
----------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL

The problem with putting a button on a worksheet is at least two-fold.
First, it's only on one sheet, so if your workbook has several worksheets, you need to duplicate the button on each of them
Second, when you scroll around the worksheet, the button will move around also, and will soon disappear off the screen. Adding a keyboard short cut - if remembered - will get around that problem.
I do use a button to run a macro on one of my worksheets, but it has only a few rows and columns so scrolling off screen is not an issue.

Wouldn't it be easier to just apply the macro to a button with the worksheet, still has the same ease of access (for the most part) and doesn't require any QAT adjustments.
If the other user uses Excel a lot they likely have their own custom QAT and have memorised the shortcut keys to access it so manipulating that might not be the best idea.

Never mind. I should have read farther into the article before posting!

Copying a QAT sounds like it might be problematical. Wouldn't overwriting my QAT with someone else's QAT erase my existing customizations?

Willy,

The method you suggest (and prefer) is actually mentioned in the above tip. (See the paragraph beginning "Of course, you may want to consider modifying how you actually made the changes to the Quick Access Toolbar.")

I had to address other ways because it was not entirely clear from Ian's situation if his customizations were solely to the QAT or if they were to the ribbon itself, as well.

-Allen

In my opinion, the simplest and safest method is to add a button to the QAT and specify to save it with the current workbook instead of "For all documents (default)". This way the buton will also additionaly show in that workbook's QAT on all other systems without changing anything else on their ribbon customasations.

"Working with the Ribbon" is also a good alernative but it is a little more complicated.

I don't see how an Add-In could solve this. As far as I know, add-ins have no special abilities of specifying what is displayed in the QAT.

(see Figure 1 below)