Where is the Excel Personal Macro Workbook Located?

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.

On Windows 7, it lives here: 1

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)”.

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:

  1. If there’s no Roaming directory in your setup, use Local instead. The rest of the path will remain the same.
Posted in Microsoft Excel. Bookmark the permalink. RSS feed for this post. Leave a trackback.

23 Responses to Where is the Excel Personal Macro Workbook Located?

  1. Kevin Woodward says:

    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!

  2. Pingback: Fun with Google Analytics | wheatblog

  3. rob blythe says:

    Most helpful, Thanks

  4. Carolyn says:

    Thank you so much!

  5. 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?

  6. wheat says:

    You can. Just name it correctly and store it in the correct location and Excel will read it on launch.

  7. 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!

  8. Sashen says:

    Thanks!!!

  9. Gary says:

    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.

  10. wheat says:

    It’s one of the formats available from the save as type menu, I believe.

  11. Pingback: Where Did My Personal Macro Workbook Go?? HELP PLEASE

  12. Pedro Belli says:

    Great advice. Thanks a lot!

  13. Christine says:

    AWESOME! Thanks!

  14. Stephen says:

    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.

  15. Jeff Stubing says:

    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.

  16. wheat says:

    Glad to help!

  17. Pingback: using excel macro when source doc not open

  18. Mahendra Verma says:

    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.

  19. Suneet Arora says:

    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 ?

  20. Claire Wilkins says:

    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!

  21. wheat says:

    @Mahendra Verma Sorry, it’s not something I can tackle right now.
    @Suneet Arora I’m not sure.
    @Claire Wilkins Glad to help!

  22. Richard says:

    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

  23. Colleen says:

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Swedish Greys - a WordPress theme from Nordic Themepark.