Where to store Ms Excel macros? What is the equivalent of Ms Word's normal.dotm? Thread poster: Hans Lenting
|
|---|
I always wondered what Ms Excel's equivalent was of Ms Word's normal.dotm...
In other words: where should/can I store generic Excel macros so that I can use them in every workbook?
Today I discovered the answer – the Personal Macro Workbook:
https://excelchamps.com/vba/personal-macro-workbook/
With Alt+F11 you open the macro editor, and there is the personal.xslb file listed:
If you want to backup this file, you'll have to use a file finder to locate it, because since the introduction of sandboxing in Ms Office, on a Mac the path is rather difficult to find:
[Edited at 2022-09-01 07:10 GMT]
| | | | Samuel Murray 
Netherlands
Local time: 17:08
Member (2006)
English to Afrikaans
+ ...
| Answering your original question | Sep 1, 2022 |
A quick google reveals that it is an XLSB file (possibly called "Personal") that sits in a folder called "XLSTART".
On my computer, there are two such folders, namely here:
C:\Users\My User Name\AppData\Roaming\Microsoft\Excel\XLSTART
and here:
C:\Program Files (x86)\Microsoft Office\root\Office16\XLSTART
both of which can be considered fairly standard, non-weird locations.
If I hadn't had a file finder (as you call it), but I knew it was calle... See more A quick google reveals that it is an XLSB file (possibly called "Personal") that sits in a folder called "XLSTART".
On my computer, there are two such folders, namely here:
C:\Users\My User Name\AppData\Roaming\Microsoft\Excel\XLSTART
and here:
C:\Program Files (x86)\Microsoft Office\root\Office16\XLSTART
both of which can be considered fairly standard, non-weird locations.
If I hadn't had a file finder (as you call it), but I knew it was called "XLSTART", these are in fact the two locations in which I would have gone to look for the folder in the first place (except that I would not have immediately thought of looking in the "root" folder of the Program Files folder).
FWIW, the way I back up my macros is by opening the macro editor, doing Ctrl+A, Ctrl+C and the doing Ctrl+V into a text file called e.g. "mymacros.txt". Then, to restore, do the reverse: I do Ctrl+A, Ctrl+C in the text file and then do Ctrl+A, Ctrl+V in the macro editor.
[Edited at 2022-09-01 07:01 GMT] ▲ Collapse
| | | | Hans Lenting
Netherlands
Member (2006)
German to Dutch
TOPIC STARTER | Life is so much simpler with Windows :) | Sep 1, 2022 |
Samuel Murray wrote:
A quick google reveals that it is an XLSB file (possibly called "Personal") that sits in a folder called "XLSTART".
On my computer, there are two such folders, namely here:
C:\Users\My User Name\AppData\Roaming\Microsoft\Excel\XLSTART
and here:
C:\Program Files (x86)\Microsoft Office\root\Office16\XLSTART
both of which can be considered fairly standard, non-weird locations.
If I hadn't had a file finder (as you call it), but I knew it was called "XLSTART", these are in fact the two locations in which I would have gone to look for the folder in the first place (except that I would not have immediately thought of looking in the "root" folder of the Program Files folder).
FWIW, the way I back up my macros is by opening the macro editor, doing Ctrl+A, Ctrl+C and the doing Ctrl+V into a text file called e.g. "mymacros.txt". Then, to restore, do the reverse: I do Ctrl+A, Ctrl+C in the text file and then do Ctrl+A, Ctrl+V in the macro editor.
[Edited at 2022-09-01 07:01 GMT]
"As I call it", well, I'm not the only one, am I: https://docs.oracle.com/cd/E19620-01/805-3899/6j3n15e7t/index.html
And yep on Windows, this personal.xlsb is indeed easy to find:
[Edited at 2022-09-01 07:08 GMT]
| | | | Hans Lenting
Netherlands
Member (2006)
German to Dutch
TOPIC STARTER | Simple can be complicated | Sep 1, 2022 |
Samuel Murray wrote:
FWIW, the way I back up my macros is by opening the macro editor, doing Ctrl+A, Ctrl+C and the doing Ctrl+V into a text file called e.g. "mymacros.txt". Then, to restore, do the reverse: I do Ctrl+A, Ctrl+C in the text file and then do Ctrl+A, Ctrl+V in the macro editor.
Sure! We all use that simple solution, don't we?
However, note that any carefully crafted user forms won't be saved with this simple procedure.
| | |
|
|
|
Hans Lenting
Netherlands
Member (2006)
German to Dutch
TOPIC STARTER | Find Any File (really!) | Sep 1, 2022 |
BTW, Samuel, since you're asking, my favourite File Finder on Mac is this one:
| | | | To report site rules violations or get help, contact a site moderator: You can also contact site staff by submitting a support request » Where to store Ms Excel macros? What is the equivalent of Ms Word's normal.dotm? | Trados Studio 2022 Freelance | The leading translation software used by over 270,000 translators.
Designed with your feedback in mind, Trados Studio 2022 delivers an unrivalled, powerful desktop
and cloud solution, empowering you to work in the most efficient and cost-effective way.
More info » |
| | Wordfast Pro | Translation Memory Software for Any Platform
Exclusive discount for ProZ.com users!
Save over 13% when purchasing Wordfast Pro through ProZ.com. Wordfast is the world's #1 provider of platform-independent Translation Memory software. Consistently ranked the most user-friendly and highest value
Buy now! » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |
Login to reply/comment 
