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: 12:35 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? Protemos translation business management system | Create your account in minutes, and start working! 3-month trial for agencies, and free for freelancers!
The system lets you keep client/vendor database, with contacts and rates, manage projects and assign jobs to vendors, issue invoices, track payments, store and manage project files, generate business reports on turnover profit per client/manager etc.
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... | | | | | |