Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff. 		Lista Forumurilor Pe Tematici
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login

POZE MRRRR'S FORUM (VIEW ONLY)

Nu sunteti logat. 		Nou pe simpatie:
Loredana12345 22 ani
Femeie
22 ani
Galati
cauta Barbat
33 - 69 ani
Mrrrr's Forum (VIEW ONLY) / Tutoriale si Ghiduri Utile // Tutorials and useful guides / [WORD, EXCEL] Personal.XLSB and Normal.DOTM Files Locations & Transfer Moderat de TRaP, TonyTzu
Autor
Mesaj Pagini: 1
Mrrrr
AdMiN

Inregistrat: acum 18 ani
Postari: 2251
What are Personal.XLSB and Normal.DOTM files?
These files are macro-enabled Excel and Word documents respectively, that are used to store your VBA code modules that you can use in any Excel and Word documents, without needing to save them each as macro-enabled files.
You can access these codes by creating shortcuts to them in your Quick Access Toolbar (QAT) or directly in the ribbons (tabs) of Excel / Word.

File locations on OS: Windows 7

Location of Personal.XLSB file:
C:\Users\USERNAME\AppData\Roaming\Microsoft\Excel\XLSTART

Location of Normal.DOTM file:
C:\Users\USERNAME\AppData\Roaming\Microsoft\Templates

Replace USERNAME with your own.

Transferring your customizations to another PC:

First of all, you need 2 files for Excel / 2 files for Word (which you transfer on your new PC via USB drive, or you send them by email)
- Personal.XLSB / Normal.DOTM you get from the locations above
- exported customizations of your Excel QAT / Word QAT, resulting in a file called Excel Customizations.exportedUI / Word Customizations.exportedUI

To export your QAT click on the arrow on its right side, select More Commands and under the frame on the right there is an Import/Export drop down list where you select Export All Customizations.

Note: if you send your files by email, put them in a password protected archive, otherwise the mail system might see the macro enabled file as a threat (since it contains code that can manipulate Windows applications).

Transferring Personal.XLSB and Excel QAT correctly:

1. Copy your Personal.XLSB to the location above and replace it if existing.
2. Open the Excel Customizations.exportedUI file in Notepad
3. Search and replace the username inside with yours; eg. search Mrrrr, replace with: TRaP. Note: this replaces your username in all the links to Personal.XLSB file present in your QAT file, as the QAT was built for the other computer and has inside the links to your Personal.XLSB file on the other PC.
4. Save the modifications and close notepad.
5. From Excel, open More Commands under QAT and import the Excel Customizations.exportedUI you modified above.

Transferring Normal.DOTM and Word QAT correctly:
1. Copy the Normal.DOTM file to the location above and replace it if existing.
2. From Word, open More Commands under QAT and import the Excel Customizations.exportedUI you exported on the other computer.

For some reason Word doesn't use the same location thing as Excel in the customizations file.

_______________________________________
pus acum 6 ani
   
TRaP
Moderator

Inregistrat: acum 7 ani
Postari: 809
To address the current user, regardless of user name - without having to modify it each time, in Start - Run or in the address bar of Windows Explorer you can type:

For a custom word ribbon file:
%appdata%\Microsoft\Word\STARTUP

For Normal.dotm file:
%appdata%\Microsoft\Templates

For Personal.xlsb file:
%appdata%\Microsoft\Excel\XLSTART

For locations for QAT files see this topic:

As you can see the entire:
C:\Users\USERNAME\AppData\Roaming

is replaced by %appdata%.

This is very helpful when putting the address into the VBA code, as you don't need to write the current user name and you can give the VBA code without having them modify anything.
pus acum 6 ani
   
TRaP
Moderator

Inregistrat: acum 7 ani
Postari: 809
To correct error on

2. From Word, open More Commands under QAT and import the Excel Customizations.exportedUI you exported on the other computer.

It has to be

Word Customizations.exportedUI
pus acum 5 ani
   
TRaP
Moderator

Inregistrat: acum 7 ani
Postari: 809
Sometimes the Normal.dotm file from %AppData%\Microsoft\Templates gets reset (as in deleted and replaced by a new Normal.dotm without macros in it.

To prevent this from happening, the Normal.dotm can be replaced with a template that is loaded the same way as a ribbon, by putting it in the folder %AppData%\Microsoft\Word\STARTUP

To create that template and replace a Normal.dotm that keeps resetting, do this:

1. Open Word (not a document), go to File - Open (or press CTRL+O), navigate to the folder %AppData%\Microsoft\Templates and open the Normal.dotm file that contains macros.

2. Go to File - Save As - navigate to %AppData%\Microsoft\Word\STARTUP and save it with the .dotm extension but with a different name than just "Normal".

3. Close all Word documents, and delete the file you previously used as Normal.dotm from %AppData%\Microsoft\Templates. Word will create a new, empty one, the first time you open Word.

4. Now the macros will work normally and should load without problems from your QAT without you having to re-create your buttons.

Editing the Modules in this template file will be a little more complicated, as you first have to disable it in Add-ins, then open the file with CTRL+O and work on the Modules, save it, then enable it in Add-ins and restart Word.


Source:
pus acum 1 an
   
TRaP
Moderator

Inregistrat: acum 7 ani
Postari: 809
updated post 2 with QATs locations
pus acum 6 zile
   
Pagini: 1  

Mergi la