macros look for PERSONAL.xlsb in wrong location

Andrew Collins 41 Reputation points
2021-12-12T22:57:06.923+00:00

The computer crashed and the Windows 10 OS was reinstalled by a professional. He placed all recovered data in "Documents" on the D drive (C being a SSD with the OS on it). Many macro enabled workbooks used for business have now been returned to their original locations (in a folder on the C drive for legacy reasons) and are working with one major exception. When any macros located in the Personal.xlsb are called there is an error message saying that excel cannot find the Personal.xlsb file in a folder in the D drive. The path in the error message includes a folder with a very long series of alphanumerics, which I assume are related somehow to One Drive. However, I have deleted the Personal.xlsb that had been copied to the D drive after the reinstallation, and replaced it in the normal C:\users\name\AppData\Roaming\Microsoft\Excel\XLSTART location, complete with all its macros. In the VBA window navigation pane Personal.xlsb is shown and when run using F5 \ F8 in the VBA window the macros work. But for some reason macros in other workbooks seem to think that Personal.xlsb is in another location. I have been through File | Options | Advanced to the General section and told Excel to "At startup, open all files in ...XLSTART" but that had no effect (and I didn't think it would). The offending line of code is
Application.Run "PERSONAL.xlsb!DeleteEbayFromCellMenu"
Given that I can see the Personal.xlsb in the Navigation pane of the VBA window, can anyone please help me with a reason why Excel is looking for it in another drive?

{count} votes

6 answers

Sort by: Most helpful
  1. John Korchok 4,696 Reputation points
    2021-12-13T18:14:16.83+00:00

    In Excel, choose File>Options>Advanced, scroll down to the General section. In the field called At startup, open all files in: and add the path to your preferred XLSTART location.

    0 comments No comments

  2. Andrew Collins 41 Reputation points
    2021-12-13T21:41:28.12+00:00

    Thank you for looking at this John. I had tried that, and unfortunately it had not effect.


  3. Andrew Collins 41 Reputation points
    2021-12-13T22:27:55.04+00:00

    Thanks for that idea John, and I'll look into it. D is the computer hard drive, with the operating system being on the C drive, which is a SSD, so I'll have to look at how to do that. My next line of attack is to cut the PERSONAL.xlsb in the XLSTART folder and paste it somewhere else, then record a new macro in a workbook to be stored in a Personal workbook so that Excel creates a new one in XLSTART. I shall then delete that PERSONAL.xlsb, and replace it with the copied one. With luck Excel will then "know" where it placed its PERSONAL.xlsb file. I'll post whether that works, though I am not optimistic.

    0 comments No comments

  4. Tom van Stiphout 1,611 Reputation points MVP
    2021-12-14T04:35:01.63+00:00

    I'll have to look at how to do that

    One way would be right-click Start > Disk Management, and temporarily give the D-drive another drive letter.

    0 comments No comments

  5. Andrew Collins 41 Reputation points
    2021-12-14T18:23:52.953+00:00

    Thanks for looking at this problem again, but it has now gone away. I would like to say that I have solved it, but it just went away. I suspect that in my many name changes, moves and copies I left a space either before or after PERSONAL. I still do not know why it tried to reference something on the D drive, but I cannot now investigate that any further. So again, my thanks.