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: 19:38 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? Anycount & Translation Office 3000 | Translation Office 3000
Translation Office 3000 is an advanced accounting tool for freelance translators and small agencies. TO3000 easily and seamlessly integrates with the business life of professional freelance translators.
More info » |
| TM-Town | Manage your TMs and Terms ... and boost your translation business
Are you ready for something fresh in the industry? TM-Town is a unique new site for you -- the freelance translator -- to store, manage and share translation memories (TMs) and glossaries...and potentially meet new clients on the basis of your prior work.
More info » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |