+ Reply to Thread
Results 1 to 9 of 9

Excel 365 Personal.xlsb location?

  1. #1
    Forum Contributor
    Join Date
    07-16-2007
    MS-Off Ver
    Excel 2003 and Excel 2016
    Posts
    178

    Excel 365 Personal.xlsb location?

    Does Excel 365's Personal.xlsb have to be located in 'AppData\Roaming\Microsoft\Excel\Startup' folder in order for macros in it to be available to other workbooks? I have it located in a user folder called 'Startup' and I have specified that Excel open any workbooks in that folder when starting, so Excel opens it upon starting. However, the only way that I can use macros in this Personal.xlsb is by using 'Application.Run "Personal.xlsb!macroname.

    The reason I ask is that I cannot seem to create a reference to that Personal.xlsb in VBA; i.e., it does not appear in available references, nor can I reference the Personal.xlsb in the user folder.

    This is not a problem in Excel 2003 where I can reference the Personal.xls in the user folder, and use macros in it in other workbooks without doing 'Application.Run'.

    Thanks.

    Update: I moved the Personal.xlsb file to the %AppData%Microsoft\Excel\Startup folder and I can now reference it under VB Tools\References Available References. That made the macros in it available to other workbooks. Is there a way that I can keep the Personal.xlsb file in a user folder and create a reference to it there?

    Thanks.
    Last edited by sumdumgai; 03-27-2022 at 01:44 PM.

  2. #2
    Forum Contributor
    Join Date
    07-16-2007
    MS-Off Ver
    Excel 2003 and Excel 2016
    Posts
    178

    Re: Excel 365 Personal.xlsb location?

    So, my question is, does Personal.xlsb have to be located in the %AppData%Excel\StartUp folder?

  3. #3
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,998

    Re: Excel 365 Personal.xlsb location?

    No, it does not.
    Everyone who confuses correlation and causation ends up dead.

  4. #4
    Forum Contributor
    Join Date
    07-16-2007
    MS-Off Ver
    Excel 2003 and Excel 2016
    Posts
    178

    Re: Excel 365 Personal.xlsb location?

    Thanks for reply. I've tried everything I can think of but I cannot get macros in 'Personal.xlsb' to be seen by other workbooks. It is located in a folder on my desktop and that folder is specified in the Excel Options/Advanced "At startup, open all files in" folder. That location is also in Trusted Locations. On Excel 2003, under Tools/References/Available References, there is a 'Personal.xls' box, which, if checked, allows the workbook to use the 'Personal.xls' macros. If unchecked, the macros are not available. On Excel 365, there is no 'Personal.xlsb' entry in Available References which can be checked. One thing I noticed was that on Excel 2003, the VBA project name is 'Personal.xls (Personal.xls)'. On Excel 365, the VBA project name for Personal.xlsb is 'VBAProject (Personal.xlsb)'. I've tried to change the VBA project name to 'Personal.xlsb (Personal.xlsb)', but that name is denied as illegal. VBA project name 'Personal (Personal.xlsb)' does not fix anything.

    This is W10 desktop. Same issue on W10 laptop.

  5. #5
    Forum Contributor
    Join Date
    07-16-2007
    MS-Off Ver
    Excel 2003 and Excel 2016
    Posts
    178

    Re: Excel 365 Personal.xlsb location?

    Correction. When I changed the VBA project name to 'Personal (Personal.xlsb)', and viewed VBA code for a book other than 'Personal.xlsb', under Tools/References Available References, the was an entry named 'Personal'. When I checked that box, the macros in 'Personal.xlsb' were available to other workbooks. And, the 'Personal.xlsb' book is not in the %AppData% tree. It is in a folder on a drive B:.

    Thanks for helping.

  6. #6
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,998

    Re: Excel 365 Personal.xlsb location?

    You should always change the project name from the default VBAProject if you want to set a reference to a workbook's project from another one.

  7. #7
    Forum Contributor
    Join Date
    07-16-2007
    MS-Off Ver
    Excel 2003 and Excel 2016
    Posts
    178

    Re: Excel 365 Personal.xlsb location?

    I see that now, but I haven't seen it documented anywhere that you have to do that for Personal.xlsb if it's not in the %AppData%\Excel\StartUp folder.

    Thanks.

  8. #8
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,998

    Re: Excel 365 Personal.xlsb location?

    It doesn't matter where the file is saved. You cannot set a reference to a project that has the same name as the original project.

  9. #9
    Forum Contributor
    Join Date
    07-16-2007
    MS-Off Ver
    Excel 2003 and Excel 2016
    Posts
    178

    Re: Excel 365 Personal.xlsb location?

    Thanks to everyone. Problem solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Personal.XLSB gone, and Excel will not create a new one
    By the machine in forum Excel General
    Replies: 3
    Last Post: 03-22-2020, 09:41 AM
  2. Replies: 1
    Last Post: 11-17-2019, 06:54 PM
  3. PERSONAL.XLSB Not Opening (Excel 2010)
    By yahoowizard in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-21-2015, 02:38 PM
  4. [SOLVED] Excel Personal.xlsb Error
    By Dnyan in forum Excel General
    Replies: 11
    Last Post: 08-07-2015, 03:05 AM
  5. [SOLVED] Personal.XLSB macro pasting into Personal.XLSB
    By davepoth in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-11-2015, 07:40 PM
  6. Replies: 0
    Last Post: 01-15-2015, 05:53 AM
  7. Personal.xlsb crashes excel
    By Lippert in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-31-2010, 06:40 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1