Microsoft Excel - personal macro workbook will not open automatically
Asked By Candice Russell on 19-Nov-12 04:21 PM
I am trying to set up macros but I continue to get the error message "Personal Macro Workbook in the startup folder must stay open for recording."
OK...so here are the steps I have taken to resolve this (none of which have worked):
1. Went to View --> Unhide -- it is grayed out...so there are no hidden items.
2. Renamed the Personal.xlsb.
3. Moved the Personal.xlsb folder and recreated it.
4. Clicked the Start --> Excel Options --> Add-ins --> Disabled Items --> Go....there wasn't anything listed.
5. I can manually open this file through Windows Explorer...but it still will not let me utilize the Macros in another existing workbook.
Please tell me that you can help me????
Harry Boughen replied to Candice Russell on 19-Nov-12 07:12 PM
Just a long shot. Can you check that
is on your trusted locations list? I'm assuming that you are using a recent version of Excel.
Candice Russell replied to Harry Boughen on 20-Nov-12 11:17 AM
Thank you for your response and help.
Yes, that is another thing I had already checked and I did have to add it to the list...just forgot to add it to the list of things I have tried. I just now confirmed that it is in the trusted location list and does allow subfolders to be trusted as well. Still getting the same error message.
I am using Excel 2007.
Harry Boughen replied to Candice Russell on 20-Nov-12 03:27 PM
You might have already tried this. This is an attempt at some sort of logical approach and I have not tested it so no guarantees.
Go to your XLSTART directory and move your personal.xlsb ( and any other file personal.xxx) to another place (desktop say). Then open Excel and record a simple macro to your personal macro file, close the file and answer yes to save the personal file etc. Close Excel. Check that there is a new personal.xlsb in the startup folder. If there is not, then Excel is obviously storing it somewhere else and you need to track down where that is. If it is there, Open excel. Open the VBA editor and check that personal.xlsb appears in the directory panel. If it doesn't, then Excel is obviously looking somewhere else for it and we need to work out where and why. If it is there, rename your old personal.xlsb to (say archive.xls) and open it and copy your macros into the new personal.xlsb then save the file. Close Excel. Open Excel and test to see if the system works. If it doesn't then perhaps the content of one of your macros is corrupt and you would have to repeat the exercise copying one macro at a time until the system breaks to determine which one is at fault.
Candice Russell replied to Harry Boughen on 20-Nov-12 03:38 PM
I have already tried this...but for the sake of my sanity I went ahead and tried it again, following your steps exactly. It does save it to the correct folder...and in Windows Explorer...it is there. But when I go in to actually use the Macro, the Personal folder is not available and 'unhide' is grayed out. Even if I open it from Windows Explorer, it will not recognize it in another file. :(
Harry Boughen replied to Candice Russell on 20-Nov-12 04:09 PM
If I understand you correctly, you are saying that Excel creates a new personal.xlsb in the correct place but that when you re-open Excel, it does not show up in the VBA edit window as a resource.
If that is the case, it suggests to me that Excel is looking somewhere else. Have you checked in options to see if the startup location has been changed?
Candice Russell replied to Harry Boughen on 20-Nov-12 05:01 PM
OK...You are my hero! But I have one last step I need to fix.
So here is what I found: Excel saves the macros in a personal.xlsb file in C:\users\candice\appdata\roaming\mircrosoft\excel\xlstart.... but it opens the Personal.xlsb file at startup from c:\program files\microsoft office\office12\xlstart. When I moved my Personal.xlsb file to this folder...it works in Excel perfectly. When I try to save another macro to my personal book...it tells me it can't find it and I can't save it...but I can save another one. Both of these locations are listed in the trusted locations.
How do I fix this? I really don't care which folder I use...but I need Excel saving to and opening from the same place. I have gone into Excel Options --> Advance --> General --> At startup, open all files in:....I have typed in c:\users\candice\appdata\roaming\microsoft\excel\xlstart...but as soon as I click OK and check it...it is gone...and it is still pulling from the office12/xlstart. I did verify spelling and I did restart Excel to verify it still isn't working properly.
I am getting closer!!! :) Thank you so much for your help!!
Harry Boughen replied to Candice Russell on 20-Nov-12 05:05 PM
Hello again Candice,
Further to my last reply can you do this.
1) Open excel
2) Hit alt-F11 to get to the VBE
3) Hit ctrl-g to see the immediate window and type this:
5) Press enter
This should tell you where Excel is actually looking. If it is not where your personal.xlsb is then move it to this location and see what happens.
Candice Russell replied to Harry Boughen on 20-Nov-12 05:16 PM
OK. I think you have solved my issues!!! Made a necessary correction and it seems to be working now. Thank you so much!!!
Harry Boughen replied to Candice Russell on 20-Nov-12 05:32 PM
Glad to hear it. Would you mind telling me what the final steps were so that I might remember it for future reference or somebody else might read it.
Candice Russell replied to Harry Boughen on 20-Nov-12 05:47 PM
Yes. It was opening the file from the wrong folder. I put in the correct folder...now it is saving to and opening the same file in the same folder! YAY!!
Harry Boughen replied to Candice Russell on 20-Nov-12 05:53 PM
Candice Russell replied to Harry Boughen on 20-Nov-12 05:57 PM
When I followed your ?Application.StartupPath command from above...I discovered that it was starting c:\program files\microsoft office\office 12\xlstart. I changed it to c:\users\username\appdata\roaming\microsoft\excel\xlstart. That fixed it completely!
Sorry...didn't think about someone else needing the same information. Hope this discription is what you were looking for.
Again...you are my hero! Thank you so much for helping me troubleshoot and fix this!!!
Harry Boughen replied to Candice Russell on 20-Nov-12 06:03 PM
And exactly what did you do to change it?
Sorry to be a pest - just for completeness.
Candice Russell replied to Harry Boughen on 21-Nov-12 10:02 AM
No problem at all.
I had already tried to add the correct location to the 'At Startup, open all files in:' and it didn't work. So I entered the C:\program files\microsoft office\office 12\xlstart, clicked OK and restarted my entire computer. Then I went back into the options and back to the At startup, open all files in: and entered the c:\users\username\appdata\roaming\microsoft\excel\xlstart.
Then I went back into the VBE and followed your directions again and the right folder was listed...finally. When I opened a file and tried to run one of my macros...it worked!!!
It was kindof a drawn out process and I don't know why it worked...but for some reason it did.
Hope this is the detail you were after. Let me know if you need anything else.
RiZ A replied to Harry Boughen on 08-Nov-13 08:23 PM
This thread was very helpful in solving part of my problem, but even after following all the steps, I still have the issue of Excel opening the personal.xlsb file from one location and saving it in a different location.
My initial issue was identical to Candice, where, my personal.xlsb file was located in C:\Users..., but it wouldn't open automatically with Excel. After reading this thread, I placed a copy of the file in C:\Program Files..., and wallah! It opened the file when Excel is opened.
However, when I add a new macro or save, it saves in C:\User..., but opens from C:\Program Files...
When I follow your steps and try ?Application.StartupPath, it shows the C:\Users... path. But it still opens from C:\Program Files.. and it doesn't allow me to change this line.
I'm not sure what to try next. Any help would be great.
Harry Boughen replied to RiZ A on 08-Nov-13 10:26 PM
Hello RiZ A,
You have to go to File, Options, Advanced, General and enter the required path in the section labelled 'At Startup, load all files in: '
You cannot do it in the VBE editor.
Hope this helps.