[Latest update: 26 April 2019]
Sometimes I blog things mostly so I can remember them and in the off chance that they might be useful to others. This post falls into that category. If you use Microsoft Excel, and you have a macro that you want to be available globally–in any open workbook–you can place it in your Personal Macro Workbook, which is just an Excel Workbook (in binary, XLSB, format, for speed) that lives at a particular location, where Excel will look for it whenever it launches. Where it lives, though, is a moving target.
On my install of Windows 10, running Excel 2016, it lives here 1 2:
C:\Users\wheatbread\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB
On Windows 7, it lives here: 3
C:\Users\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB
On Windows XP, it lives here:
C:\Documents and Settings\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLSB
In either case, the easy way to create PERSONAL.XLSB is not to muck around with the file system directly. Instead, just record a macro in Excel and, when you’re prompted to save it, choose Personal Macro Workbook from the Store Macro In drop-down menu on the Record Macro dialog box. Record yourself typing a few numbers and adding some formatting to them or something similar. Afterwards, you can open up the Personal Macro Workbook via the Visual Basic button on the Developer tab later and delete whatever you record. Once you create it, your Personal Macro Workbook will be listed in the VBA Editor as “VBAProject (PERSONAL.XLSB)”.
Finding the Path
The best way to find the path–hat tip to commenter Kevin Woodward–is to use the Visual Basic Editor (VBE). These days, both the Mac and Windows versions of Excel support VBA and have basically the same VBE. The only difference is the shortcut keys you use to interact with it.
On Windows:
- Press alt+F11 to open the VBE.
- If the immediate window is not visible, press ctrl+g to make it so.
- In the immediate window, type ?Application.StartupPath
- Press enter/return
On a Mac:
- Press fn+option+F11 to open the VBE.
- If the immediate window is not visible, press ctrl+command+g to make it so.
- In the immediate window, type ?Application.StartupPath
- Press enter/return
The path to the XLSTART folder will appear, even if it is hidden in Windows.
Some Additional Details for the Truly Nerdy
The location of the Personal Macro Workbook is a little confusing, because older versions of Excel stored it in an XLSTART folder buried inside the Program Files folder. Even though you can’t write any files to it, Excel 2007 and Excel 2010 also have XLSTART folders inside the Program Files directory. Here are the paths:
For Excel 2010:
C:\Program Files\Microsoft Office\Office14\XLSTART\
For Excel 2007:
C:\Program Files\Microsoft Office\Office12\XLSTART\
“Office 14” is the internal name for Office 2010. It’s actually the 13th version of Office, but Microsoft saw fit to skip naming it Office 13, due, one suspects to superstition. So “Office 12” is Office 2007, “Office 11” is Office 2003, and so on. If you happen to be stuck running Excel 2003 on a Windows XP box, you’ll find PERSONAL.XLSB at this path:
C:\Program Files\Microsoft Office\Office11\XLSTART\
.
Notes:
- In this example, “wheatbread” is my username. It’s probably not also your username, unless we both have a particularly weird nickname. ↩
- If there’s no Roaming directory in your setup, use Local instead. The rest of the path will remain the same. ↩
- As in the Windows 10 example, if there’s no Roaming directory in your setup, use Local instead. The rest of the path will remain the same. ↩
James,
Regarding the startpath where the Excel Personal Macro Workbook is located. This location can be installed in several different locations on the PC or company network. The best advice for your readers (like me) is explained on Ron de Bruin’s website: http://www.rondebruin.nl/personal.htm
1) Open excel
2) Hit alt-F11 to get to the VBE
3) Hit ctrl-g to see the immediate window and type this:
4) ?Application.StartupPath
5) Press enter
You’ll see the startup path returned for the PC.
Thanks for your blog, I am an avid reader!
Most helpful, Thanks
Thank you so much!
Cool advice, though I liked suggestion of Mr. Woodward but what if a personal.xlsb is not located there? Can we create a new one that will treat like default or what?
You can. Just name it correctly and store it in the correct location and Excel will read it on launch.
Also, you can use the enviromental variable %APPDATA% to get the base of the folder – for instance the path “%APPDATA%\Microsoft\Excel\XLSTART\” would reach the correct folder in most cases. If not, try replacing %APPDATA% with %LOCALAPPDATA%. For instance, just typing %appdata% at the start prompt opens the explorer at Documents and settings/username/application data”. Handy!
Thanks!!!
I just changed to Office2010. PC died and new one has 2010. Previously, I was using Office2003. The Personal file is .xls. How do I convert it to .xlsb? I appreciate the other info.
It’s one of the formats available from the save as type menu, I believe.
Great advice. Thanks a lot!
AWESOME! Thanks!
You can also hide and unhide the Personal WB. This is handy if you are often working with the VBA editor, since it wants you to have the workbook visible to edit the macros. If you are not editing them, it is better to have it hidden, or else it will open the Personal workbook each time you start Excel.
Thanks for taking the time to post this. My work laptop just got upgraded to Windows 7, and all of a sudden my macros no longer work – and I rely on them intensively. This was a big help.
Glad to help!
I wnat to create a macro (UDF) which reads data from a file (or worksheet). I would like create a worksheet in personal workbook and save the data on it. There is a sheet 1(default) on personal workbook, but I cannot see it. If there is way to create a worksheet on perosnal workbook, please help me.
I have got XLSTART folder at two paths on my PC (m using Excel 2010):
1) C:\Documents and Settings\suneetaro\Application Data\Microsoft\Excel\XLSTART
2) C:\Program Files\Microsoft Office\Office14\XLSTART
Both don’t have PERSONAL.XLSB (not even hidden), but I had created a macro with ‘All open workbooks’ option selected for ‘Macros in’ dropdown ! Where the macro got stored then ?
This was a real help to me as I accidentally closed the personal macro file and needed it open to continue to record macros (which I’m still a novice to intermediate at (practice makes perfect) – work have conveniently hidden my xlstart folder (a matter of annoyance) – although after sending them a technical email making them sound like I knew what I was doing (I do for the most part), they’ve now sent me a secret link that opens it & I can now do other things that I like to do with Excel that requires the xlstart folder (like have the worksheets and workbook open up in a format I like). Not sure work will like me doing that, but tough!
@Mahendra Verma Sorry, it’s not something I can tackle right now.
@Suneet Arora I’m not sure.
@Claire Wilkins Glad to help!
I have the same issue as Suneet and Search does not ‘find’ the file, i’ve stored about 6 macros today- can see them- but can’t edit them!…#frustratednovice
Thanks so much for posting this!! I have a new computer and really don’t want to recreate all of my macros so this will help a ton.
I needed to do this for 500 machines and users who may or may not of had a personal.xlsb file
we are using USMT 5 to transfer from windows XP Office 2007 to Windows 7 Office 2010.
I didn’t find much online and wanted to post this in case someone needs it as was quite difficult to work out if you add this as and extra config xml it will back up and restore the file and macros run fine on new machine
Copy Files from XP office 2007 Folder to Win7 office 2010 Folder
MigXmlHelper.GenerateUserPatterns (“File”,”%USERPROFILE%\Application Data\Microsoft\Excel\XLSTART\* [*]”,”TRUE”)
MigXmlHelper.GenerateDrivePatterns (“* [*]”,”Fixed”)
Hope this saves someone some time
Copy Files from XP office 2007 Folder to Win7 office 2010 Folder
MigXmlHelper.GenerateUserPatterns (“File”,”%USERPROFILE%\Application Data\Microsoft\Excel\XLSTART\* [*]”,”TRUE”)
MigXmlHelper.GenerateDrivePatterns (“* [*]”,”Fixed”)
I have used the “Record a Macro” in the past to recreate my personal workbook, but the last time I tried, it gave me a message that said something like, “In order to record a macro, the personal workbook needs to be open and running.” It then said, “Unable to record macro.” And that was it. I’m still left with no personal workbook.
Hi, Thanks for this but I do not get how to save macros that I have already created in one project to this personal folder…I cannot see the option to save to personal workbook in the macro menu nor in the file save as menu….I am on windows 7 with excel 2010.
Thanks
Hi, I’ve a related question:
Is it possible to change the location of PERSONAL.XLSB?
I do backup things with dropbox, so I want to place it somewhere there. It works with the startip path, but apparently not for PERSONAL.
Windows 7 / 8.1 Excel 2011
Thanks!
Hello, I currently using Excel 2010 and when i record a Macro and the drop doesn’t list PERSONAL WORKBOOK to select how do I get it in the drop down? Any feed back is much appreciated.
Thanks,
If this helps anyone else, I put some of my technical files to make life easier for myself on web-based shared drives (i.e. Google Drive, DropBox, etc.) I have an .xlsb file I put out there with the standard macros I want with me anywhere I go, and I just now created a basic .bat file out there as well that will copy that .xlsb file into the proper Windows 7 directory given the info I got from this page for %APPDATA%.
Below is all that’s in the .bat file I created to make this work. You’ll have to modify your own username and path, but feel free to try it! :-)
:: Copy my personal .xlsb file from my Google Drive to the Windows 7 Machine I’m using
copy C:\Users\\”Google Drive”\”PERSONAL with macros for combining xlsx spreadsheets.xlsb” %APPDATA%\Microsoft\Excel\XLSTART\personal.xlsb
For some reason that first path got messed up; I had my username in the greather than less than brackets after “Users” but it’s not showing so I’ll just call the field USERNAME:
:: Copy my personal .xlsb file from my Google Drive to the Windows 7 Machine I’m using
copy C:\Users\USERNAME\”Google Drive”\”PERSONAL with macros for combining xlsx spreadsheets.xlsb” %APPDATA%\Microsoft\Excel\XLSTART\personal.xlsb
Thank you
Thanks for this blog post. I wondered where personal.xlsb was saved!
As it is saved in the user profile, which is typically saved on a pc’s local hard disk, even in a corporate setting, I would recommend getting in the habit of *copying* the personal.xlsb to another location so that you have a backup copy in the event of a hard disk failure, complete computer failure, the IT dept replacing your pc without checking with you, or your profile needing deleting and recreating. That latter used to be a common solution when I worked in IT support in the NT and XP days, when it was a quick fix solution to users complaining their pcs were running slowly. Don’t know if IT departments still use it as a quick fix for problems, but I’ve learned to make sure I keep a copy of anything important that’s in my user profile.
Using the %APPDATA% and %LOCALAPPDATA% variables is good advice.
I too like to share macros with my desktop and laptop without having to copy files back and forth to dropbox. My solution was to place the personal.lxsb file in my dropbox location and simply place a shortcut to it in the xlstart directory (the location of which is the subject of this topic). The first time you open excel after this change you will get a security warning so hit “enable macros” and continue. Once inside excel, to file->options->trust center->trust center settings->trusted locations and browse to the dropbox or google drive folder where you store the macros worksheet. This will stop the security warning on entry.
I noticed that two people have the same problem as I. I can’t find my personal macro anywhere, not even on the XLSTART folder, not even when I select to show hidden folders. It’s a ghost that I would like to find and “kill”. Jokes aside, I would really like someone to help me with this problem.
You can access an earlier version of Excel Workbook PERSONAL?
@John Queeno..
Im not expert but i think u can try this to find your personal macro..
Excel 2013
1)Open your worksheet.
2)Click on view tab.
3)Click Unhide.
4)Choose your personal.xlsb and click okay.
5)Automatically will open your personal.xlsb.
6)Click the file tab at the left top.
7) After that, there have Relate Documents.
8)You can open file location to locate your PERSONAL.XLSB.
Thats all.
For years this has been a problem & knowingly or not YOU HAVE THE ANSWER !
Excel creates the workbook PERSONAL.XLS at location “A” but does NOT load or run it from that location.
The Dir at the location you suggest “C:\Program Files\Microsoft Office\OFFICE11\XLSTART\” is empty. I simply copied the file ( PERSONAL.XLS) from A to B & now PERSONAL.XLS works just fine! I use XP & Excel 2003 Thank you. Thank you. Thank you.
Received a PC Refresh at work which has Windows 10. Your instructions were spot on except for C:\Program Files\Microsoft Office\Office14\XLSTART\ part. I had to go to C:\Program Files(x86)\Microsoft Office\XLSTART. Than you for posting!!!
Thank you so much. I had a computer crash and had to find a clear answer of where to put my Macro file.
Slight mistake for Windows 7 path
Correct path for user “CK” is
C:\Users\ck\AppData\Roaming\Microsoft\Excel\XLSTART
you have written “C:\Users\AppData ….
hope this is useful
I have no idea why Microsoft would have made such a rubiks cube out of saving macros in a spreadsheet. I want to save a spreadsheet as a template with its own macros to expedite work and not have it interact with any other spreadsheet but the one I create for this purpose. I have no desire to hide it or put it on a different drive. Why make things difficult? God…I miss Lotus 123!!!
Kevin Woodward’s reply of 20/12/2011 is excellent.
Thanks Kevin and thanks to Ron de Bruin’s website.
Help please!
I use Windows 7 with Office 10
I have first started using Excel 3 years ago on daily basis and made my first macro line then.
When wanted to save it it asked me the name and the folder to save
I picked up the name Macros.xls and Download folder (since all my files were in that folder ).
Then any day I pull up a report that I received through email I see it includes all the buttons I created on Macros.xls file and as soon as I click on any of those buttons the Macros.xls file automatically pulls up and then the macro assigned to that button is run.
So far so good until the time for some reason I moved all my files from the Download folder to another folder. Now as soon as I clicked on any of those buttons that I assigned to the macros an error response pops up that cannot find the Macros.xls file!!
I had to move the Macros.xls file from new folder back to download folder again in order it works again and I can run the macros on new Excel spreadsheet that I receive every day to work with!
I searched everywhere and all sites including this here saying the macro file is PERSONAL.XLSB and is in XLSTART folder either on C:\Users\All\AppData\Roaming\Microsoft\Excel
Or
“C:\Program Files\Microsoft Office\Office14”
( C:\Program Files (x86)\Microsoft Office\Office15 on my computer)
But in both cases the folder XLSTART is empty!on both paths!
Now I need to be able to rename the file (Macros.xls) and change the folder and wondering how !
Anybody knows how to I appreciate to give me a hint.
Just checked on Windows 10 (Excel 2016) and the location, in my case, is here:
C:\Users\YourUserName\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB
I’ll fold that into the article, when I get a chance. But I wanted to note it here.
Please be aware path may be on shared network when users are roaming users.
So it gives this syntax on W10 + Off2016:
\\[Server name]\[Shared folder]\[User account]\AppData\Roaming\Microsoft\Excel\XLSTART
So just use “Application.startupPath” as it is reliable. All others hints will launch you in a wall of concrete one day.
Thank you so much for your help!
Thanks so much. i was able to find it using the vba code ‘?Application.StartupPath” as nothing else worked (even the microsoft site was wrong as it was not under ‘local” as they suggested but was under “roaming”) and was able to get rid of the extra one (personal autosaved)
Thanks mate! Appreciate your efforts… and your humour (re: weird nickname). ;-)
All the best!