How to migrate Microsoft Office settings?

This is an extension of a previous post found here.

I am looking for a free solution to transfer all settings for all Office 2010 programs (I currently use Word & Excel, but occasionally use Access, PowerPoint, Outlook, etc.). The best possible option would be to synchronize settings between my laptop Office 2010 installation and desktop automatically, however I do not think Office 2010 provides such functionality.

The customizations I would like to migrate/sync:

• Ribbon & Quick Access toolbars (this is the easy one - just go through the Options under each of these tabs, select Import/Export Settings > Export)
• ALL information in each tab of the Options menu. This includes details down to the kerning of the font. More generally, I want to set things like my default font to, let's say, Tahoma size 11 on each & every new document, then have that setting migrated/synced to the other computer.
• Default document metadata: This may be a function of the working template, though... which is a customizable setting in the Options menu. See 2nd item above.
• All of the default pre-set table styles in Excel, for example, or the pre-set heading styles in Word.
• Anything else I'm missing that is customizable outside the bounds of these items.

Am I just shooting too high for hopes like these? Secondly, am I shooting extra high by asking for a free solution, since I've already purchased the two licenses to Office 2010?

Solution 1:

Have you tried the Windows 7 migration tool? It's meant to transfer all app settings, but it could be used for this purpose. Here's a link from Microsoft about using it to migrate office. It mentions that the machines have to be the same bitness (either both 32 or 64) and there is one option in Word that is not transferred.

Solution 2:

I have this problem regularly, and I usually try to solve it by manually transferring my settings. I admit that this is pretty time-consuming (at least when you are not used to it), and I, too, would prefer having a tool that just does it for me. But on the other hand, it is a way solving the issue without the need of using third party software.

Here is what I do:

Word

Description should work for Word 2010, 2013, 2016...

1. Search for the "Normal.dotm" file in the "ApplicationData... \Templates" Folder. For me, this is C:\Myname\AppData\Roaming\Microsoft\Templates, but the proper path may vary depending on your system, and so may the folder names (In German, e.g. it is not "Application Data/Templates" but "Anwendungsdaten/Vorlagen".) Copy the Normal.dotm file into the respective folder on your new machine. (It may take some time for you to find out what folder this is. And of course you need access to this folder. If you happen to work in a larger organisation, you may not have access to this folder. Sorry.)

2. In the same "Templates" folder, you will also find your Word document templates, e.g. for your official letters, serial letters or whatever, named xxxx.dot, e.g. OfficialLetters2016.dot or so. Copy those as well to the respective folder on the new machine.

3. Transfer your settings for the "Ribbon": File --> Options --> Customize Ribbon --> Import/Export (a button that is placed right hand side, to the buttom of the screen). This will let you save a file typcially called "WordCustomizsations.exportedUI". Save it somewhere (you may chose a different name, it serves only to be identified by you yourself), and transfer it to your new machine. There, you would open the same dialogue and choose "import" and point to your file.

4. You may have a number (quite a lot, maybe) of paragraph styles that you are used to use. To transfer them, create a new Word document (it may be an empty one, just a blank sheet of paper), save it and transfer it to your new machine. There, open it. Then search for the "Manage Styles" function. (It may not be available in your ribbon; then, search for e.g. via Files --> Options --> Customize Ribbon --> Choose commands...) The "Manage Style" dialogue, with its "Import/export" button opening an "Organize" dialogue, lets you list the styles stored in one document and copy it over to another document, or - which is what you want - into the Normal.dotm file.

Note: Step 4 should actually be unnecessary if you succeed to copy your Normal.dotm onto the new machine. If not, Step 4 gives you an option to copy your styles anyway. However, you will not be provided with all the shortcuts you are used to. These you will have to create from scratch (if you could not copy your Normal.dotm).

5. You may have a number of Word macros (i.e. VBA scripts) that help make your working day a more joyful or easy one. To copy these, you have to open the Visual Basic Editor (via the "Developer" section of the Ribbon; if you do not see this, you probably do not use such fancy stuff as macros anyway), browse there for the "Normal" section, and therein, copy everything which seems to be personally yours, with special respect to the "Forms", "Modules" and "ClassModules" folders. Unfortunately, you cannot copy all files at once. You have to right-click every single file separately and choose "Export" from the context menu. This then will save the file as a .frm, .frx and .bas file into a location you choose. Copy all those file to your new machine, and import them to the proper place (again "Normal" section in the Visual Basic Editor) by choosing "Import file" from the context menu.

Note: Step 5 may be unnecessary when you did Step 1 (migrating your Normal.dotm onto the new machine). To check it, after transfering normal.dot to your new machine, and starding Word, see if your macro functionality is there. You can also look in the VBA editor if the "Modules" and other stuff are there. - Note of 19 April 2021: I set up a new machine (Win 10 plus Office 2010, and transfering Normal.dot indeed made all my macros be present on the new machine as well.)

6. You may be using customised AutoCorrect entries (having replaced the silly ones that Microsoft deliveres by default). You will find your AutoCorrect entries in a folder like C:\Users\YourName\AppData\Roaming\Microsoft\Office, in the form of a number of xxxxx.acl files. Copy them over to the respective place on the new machine.

7. You may have your own "User Dictionaries", i.e. files where Word stores all those word that you tell the spellchecker to be correct although it highlights them as wrong. (The more elaborate your language is, the more Word will think your spelling is wrong...). User dictionaries are stored typically in C:\Users[you]\AppData\Roaming\Microsoft\UProof, and are called something like CUSTOM.DIC or WHATEVERNAME.DIC. (This path is definitely so in Windows 10 and Office 2016. Also for Windows 10 plus Office 2010. Also for Windows 7 plus Office 2010. For other versions I have not yet check. I will update this post any time I learn something new.)

Excel

Description should fit Excel 2010, 2013, 2016...

In Excel, the procedure is less time-consuming

1. Copy the Ribbon settings (similar procedure as above Step 3)
2. Similar to Step 5 above, copy your macro code (VBA), forms etc. from the Visual Basic Editor, section "PERSONAL.XLSB" to the same place on the new machine.

Nota bene: I am speaking here of macros that you want to be available always, in any Excel worksbook (not only in one specific workbook!). These macros need to be saved in a file called PERSONAL.XLSB, and for the macros to be available, Excel must load this file when starting up. To this end, this file must sit in a specific folder on your system (see below).

In a freshly set up system, you may not have a PERSONAL.XLSB file yet. This would typically be the case when you start working in a new organisation, and they have just created your user account.

To create one, just pretend you want to record a macro (via the "Developer" tab in the ribbon, then "Record Macro"). This will create a PERSONAL.XLSB. You have to make sure you save this.

A PERSONAL.XLSB is practically a workbook, but it is usually hidden (not visible). To make it visible (which is a good idea when you want to store it), click VIEW --> UNHIDE --> PERSONAL.XLSB

Location of the PERSONAL.XLSB file on your system:

In Windows 10, the place is:

C:\Users\[user name]\AppData\Roaming\Microsoft\Excel\XLStart

In Windows 7, Microsoft says it is

C:\Users\[user name]\AppData\Local\Microsoft\Excel\XLStart

but if it isn't there, try the the path stated above for Windows 10.

In Windows XP, Microsoft says it is:

C:\Documents and Settings\[user name]\Application Data\Microsoft\Excel\XLStart 

Workbooks in the XLStart folder are opened automatically whenever Excel starts, and any code you have stored in the personal macro workbook will be listed in the Macro dialog,

With your macro modules copied into the new system, the shortcut keys you assigned to the macros, should be working, too.

Once you have a PERSONAL.XLSB, you can import your existing VBA code scripts ("Modules") and form elements as describe above under Word, Step 5.

Attention: you have to save this!

Outlook

(Tested in Office 2016)

1. Macros: similar

2. Ribbon settings: similar

3. One more thing to do is to take care of archived e-mails. Archived e-mails typically are stored in a .pst file typically on the hard drive of the computer (but they can sit anywhere else too). To find out where they sit, click File --> Account settings --> Account settings (yes, twice) --> Data files. You should get a path displayed for archive files. Copy those files (it may be only one) to your new machine. Then tell outlook on the new machine where the archive file (I usually call it simply archive.pst) is stored, in the same dialogue.

4. E-Mail templates: like Word document templates (see above) these are in the same "Templates" folder as the "Normal.dot", i.e. typically C:\User[you]\AppData\Roaming\Microsoft\Templates, and you copy them into the respective folder on the new machine. These template file are called xxxxxx.oft

OneNote

(Tested in Office 2016)

1. Macros: much to my disappointment, OneNote does not have the option to improve it by self-made VBA macros. :-(

2. Ribbon-settings: as in Word and Excel

3. Copy your notes. They may be stored in one xxxxx.one file, or in several of them. You can check their location via File --> Options --> Save & Backup. The dialogue window will show where those files sit, and you can then access them via your favourite "Windows Explorer" replacement (I strongly recomment Xplorer2 by Zabkat)... or of course via "Windows Explorer" itself (if you have masochistic tendencies).