Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Personal.XLSB in Excel 2010 does not open on starting Excel


(!)

Tacit's Avatar
Tacit Tacit is offline
Member with 18 posts.
THREAD STARTER
 
Join Date: Apr 2012
14-Apr-2012, 09:10 AM #1
Solved: Personal.XLSB in Excel 2010 does not open on starting Excel
Problem in Excel 2010 [but revives a Closed TSG thread for Excel 2007]

The "Personal Macro Workbook" is a file called Personal.XLSB
The Personal.XLSB file is in the correct location /XLSTART/
The file properties option "Opens with Excel" is selected.
The file contains a (freshly re-made) macro.
(It also contained the same steps macro before it was deleted
and re-recorded during troubleshooting for this problem.)

When Excel is opened, the Personal.XLSB DOES NOT OPEN WITH it.

Because the Personal Macro Workbook does not open,
- existing macros do not function,
- nor can new macros be recorded

Same not-opening result, whether opened by clicking on
- Excel, in the Programs list or
- a desktop shortcut to a specific Excel file

However, the Personal.XLSB file can be opened and the macro made functional
by manually opening that file where it is stored in /XLSTART/

An unexplained workaround was identified in the closed thread.

*rename the Personal.XLSB to Personal.XLSA

I find this workaround effective.
The Personal.XLSA file opens with Excel and the macros are available.
But the file seems to be grayed out when viewed with Explorer in /XLSTART/

However, I do not like using workarounds, as they can cause trouble later.
I find that the "open with" setting was changed when the extension was changed
The setting is now "Opens with: Windows Shell Common"

I would appreciate an explanation how to make Personal.XLSB open as it should.
Or at least an explanation as to why the use of .XLSA is an acceptable alternative.

Thanks.
Tacit's Avatar
Tacit Tacit is offline
Member with 18 posts.
THREAD STARTER
 
Join Date: Apr 2012
15-Apr-2012, 03:54 PM #2
Update. No surprise.
The workaround (change extension to .XLSA) does not 'work' after all.
Today, the new file Personal.XLSA does open with Excel
or at least shows the macros in it on the Macros list in Developer view.
However, attempts to record a new macro meet with the original error message,
Personal Macro Workbook in the startup folder must stay open for recording.
...square one...
Garf13LD's Avatar
Garf13LD Garf13LD is offline
Member with 455 posts.
 
Join Date: Apr 2012
Experience: Intermediate
17-Apr-2012, 02:48 AM #3
Tacit's Avatar
Tacit Tacit is offline
Member with 18 posts.
THREAD STARTER
 
Join Date: Apr 2012
25-Apr-2012, 06:04 PM #4
still need help
Garf's link is to a solution that may have worked in Office 2007 but that does not apply to Office 2010. Therefore, I remain limping along on the workaround .XLSA and wish to have a permanent fix.
Garf13LD's Avatar
Garf13LD Garf13LD is offline
Member with 455 posts.
 
Join Date: Apr 2012
Experience: Intermediate
26-Apr-2012, 05:12 AM #5
I had this problem before in Excel 2010, so there is no reason it is not working.

1. Make sure that personal.xxx is not in disabled section of Add-in.
2. Go to ur trust centre and make sure that user auto start and excel auto start is pointing to valid folder.
3. Make sure whatever file u want to load is there.

For me, my file is personal.xls (dunno why still in old version) and resides in user auto start.
Attached Thumbnails
Solved: Personal.XLSB in Excel 2010 does not open on starting Excel-auto-start.png  
Tacit's Avatar
Tacit Tacit is offline
Member with 18 posts.
THREAD STARTER
 
Join Date: Apr 2012
26-Apr-2012, 07:26 AM #6
Location of PMW
Garf, I very much appreciate your time in trying to help me here. I am rather out of my depth with this problem of the Personal Macro Workbook (PMW). It seems that (at least my copy of) MS Office/Excel is different from what would be expected by the suggestions you offered earlier (and that previous threads seem to have proposed for Excel2007).

I am currently able to use the one macro that I recorded when my PMW had the default extension .XLSB.
Since then, I tried a couple of times to record another macro (with PMW .XLSA) but am unable to do so.

The default correct location of the .XLSB file in my Excel is:
C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART
This is the path given in the Help command for "Pesonal Macro Workbook".
When I navigate to that directory\file and examine the file properties, it says "Enabled".

The solutions that were offered (in other threads of TSG) for 2007 proposed going to About > AddIn and enabling the .XLS PMW. But in MSO2010, there is no such "pathway" or screen.

I do have File > Options > AddIns but that leads to a screen with no reference to the PMW.

I do have File > Options > Trust Center (in which there are 11 possible views)

File > Options > Trusted Locations shows the pathway (above bold) to the subdirectory
which I guess is also a confirmation it is available (five other pathways are shown).
[I don't know how to insert a screen shot here.]
Garf13LD's Avatar
Garf13LD Garf13LD is offline
Member with 455 posts.
 
Join Date: Apr 2012
Experience: Intermediate
26-Apr-2012, 10:10 PM #7
Is your PMW opened at excel startup?
Go to view, unhide and you should be able to see it if it is loaded.

If it is not loaded,
1. try launching PMW by double clicking the file. What do you get?
2. try putting a normal file in that folder to see if it loads up.
Garf13LD's Avatar
Garf13LD Garf13LD is offline
Member with 455 posts.
 
Join Date: Apr 2012
Experience: Intermediate
26-Apr-2012, 10:53 PM #8
On further check there is no reference to xlsa, either u save it in xlsb, xla or xls.
Ziggy1's Avatar
Ziggy1 Ziggy1 is offline   Ziggy1 has a birthday soon! Ziggy1 has a Profile Picture
Computer Specs
Member with 2,525 posts.
 
Join Date: Jun 2002
Location: Ontario,Canada
Experience: Advanced
26-Apr-2012, 11:08 PM #9
Quote:
Originally Posted by Garf13LD View Post
Is your PMW opened at excel startup?
Go to view, unhide and you should be able to see it if it is loaded.

If it is not loaded,
1. try launching PMW by double clicking the file. What do you get?
2. try putting a normal file in that folder to see if it loads up.
you are on the right track, while the personal.xls is unhidden, open the "Properties" dialogue and see the path it is pointing to. once you clarify that, take a copy of your old Personal.xlsb and drop it into the same directory ( while Excel is closed) and overwrite the one that is there. Basically any file (with macros) you save there will load your Macros when excel starts.... it does not even have to be called Personal.xlsb, which is why an xls copy wll also work.

*** again to clarify.... don't go to the folder where you think it should be, use the Unhide and confirm the location Excel is actually opening.
Tacit's Avatar
Tacit Tacit is offline
Member with 18 posts.
THREAD STARTER
 
Join Date: Apr 2012
27-Apr-2012, 10:57 AM #10
listening with docility
1. My current PMW opens with Excel
1.a. However, that PMW has a workaround ext .XLSA (contrary to Excel2010 manual)
1.b. the one macro in it works as designed [enter current date to cell]
1.c. attempts to record an additional macro in the PMW meets with the following error message
"Personal Macro Workbook in the startup folder must stay open for recording."
and (after OK) "Unable to record."
1.c.2 [Note that this is the same as the original problem I had with PMW ext .XLSB]

2. Choosing View > Unhide reveals that the PMW with .XLSA is open
2.a. I originally did find that the correct .XLSB PMW opened manually (dbl-click) and worked properly after so being opened. But I wanted to have the PMW open with Excel, which it seemed to have the settings to do (and as the workaround .XLSA now does).
2.b. see above 1.c.

3. I cannot find the "'Properties' dialogue" for an open file in Excel 2010,
although I seem to recall it was in the options of the File command of Excel2003.
3.a. To confirm the location of the actual (vs. supposed) location of the PMW,
I entered the formula =cell("filename") into a cell of the Unhidden PMW
which showed that it is indeed in the correct directory (above in bold).
C:\Users\Tacit\AppData\Roaming\Microsoft\Excel\XLSTART\[PERSONAL.XLSA]


4. I created a file "TEST.xls" and saved it in the location where my PMW is:
C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART
4.a After closing and (5 mins later) reopening Excel, the test file did "Open with Excel"
and Unhide reveals that the .XLSA PW is also open.
4.b. see above 1.c.

ADDITIONAL TRIALS/TESTING DONE
5. I changed the PMW ext from .XLSA back to .XLSB
5.a. so named, it does not "Open with Excel"
5.b. however, after opening it manually (dbl-click)
5.b.1. the (first) macro functions as designed
5.b.2. it is possible to record an additional (second) macro
5.c. after saving with ext .XLSB, see 5.a.
5.c.1. after re-opening manually, both macros function as designed

6. I changed the PMW ext from .XLSB back to .XLSA
6.a. so, named, it does "Open with Excel"
6.b. also, both macros function as designed
6.c. it is possible to record an additional (third) macro
6.c.1 however, after saving, closing, and re-opening,
the (third) macro is not found in .XLSA
6.c.2 HOWEVER, the (third) macro is found in
a new automatically created .XLSB PMW
6.c.3 If the new .XLSB is opened manually, its single (third) macro functions as designed
6.c.3.1 That is, the new .XLSB does not contain the macros that are in the .XLSA PMW

I do want my MSO 2010 to operate according to the manual and to be able to create a more macros. I am, of course appalled to think that (as I have found in the past is usually the case), the problem is due to some incredibly simple setting change that I have wrongly and unconsciously made. I greatly appreciate the time and expertise of contributors here helping me, and I have endeavoured to try the suggestions to the best of my ability.
Zack Barresse's Avatar
Computer Specs
Member with 5,434 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
29-Apr-2012, 11:53 AM #11
Hello,

You do NOT want to change your Personal workbook to an XLSA extension. You want it in XLSB format. You can have it in XLS format, and if you do it's probably because you had one previous to upgrading to 2007+. It will work just fine, although the file itself will not be able to take the newer functionality 2007+ have to offer (some people write to their Personal.xlsb to save data for current session info read/write).

When you open a fresh instance of Excel (all instances closed, then open Excel), hit Alt + F11, then hit Ctrl + R. This will open your Visual Basic Editor (VBE) and the Project Explorer (PE) pane. It will list all open workbooks, hidden or not. The Personal.xlsb file should be open but hidden under normal operation. Do you see it in the Project Explorer?

If you're using 2007, when someone says click "File", they mean click the Office button, the big round thing in the top-left corner of the application window. In 2010 it is literally named File again. It was too confusing for people to get used to an "Office button" so they went back to naming it "File", but they did change what it opens and now call it the Backstage View, as it takes over the entire screen, and 2007 does not.

So click the Office button and then at the bottom you see Excel Options (usually just called Options). Click Trust Center and then go to Trust Center Settings. Let's check a few things
  • Trusted Locations: The checkbox "Disable all Trusted Locations" should NOT be checked.
  • Trusted Documents: The "Disabled Trusted Documents" should NOT be checked.
  • Add-ins: No checkboxes should be checked.
  • Macro Settings: Anything except the first radio button can be checked. You should also check "Trust access to the VBA project object model"

Can you confirm those settings?
Ziggy1's Avatar
Ziggy1 Ziggy1 is offline   Ziggy1 has a birthday soon! Ziggy1 has a Profile Picture
Computer Specs
Member with 2,525 posts.
 
Join Date: Jun 2002
Location: Ontario,Canada
Experience: Advanced
29-Apr-2012, 12:47 PM #12
to find the properties see here...

http://www.addintools.com/documents/...roperties.html

then click the General Tab. That should tell you the exact location of the personal file... make sure you have it unhidden and active when you do this step.
Tacit's Avatar
Tacit Tacit is offline
Member with 18 posts.
THREAD STARTER
 
Join Date: Apr 2012
29-Apr-2012, 04:06 PM #13
confirmation
Ziggy and Zack, thanks for your attention.

Zack

Paragraph 1
From XP & Office 2003 (since May 2004)
I went directly (2011 Dec 26)
to Windows 7 & Office 2010 [Home and Student].

It may be worth mentioning that the software was also installed on a new computer. No program files from the old computer were brought over; only files.


I confirm the first two sentences of your note. And I would not forego any of the funtionality of the new software.

I do not understand the parenthesis "(some people write...)".


Paragraph 2
After freshly opening Excel and keying ALT-F11 & CTRL-R
[CTRL-R seems to have no additional effect after ALT-F11]
a. the VBE opens displaying Personal.XLSa with one macro (FixValu)
I do not know how to determine whether it is "hidden" in this view
b. VBAProject (PERSONAL.XLSa) shows two directories
b.1. Microsoft Excel Objects contains two items
b.1.1. Sheet1 (Sheet1)
b.1.2. ThisWorkbook
b.2. Modules
b.2.1. Module1
b.2.2. Module2


Paragraph 3
I am not using nor have I ever had 2007 on my computer.

Paragraph 4

I clicked on File > Options > Trust Center > Trust Center Settings
[the view did not take up the whole screen]

Trusted Locations: "Disable all trusted locations" is not checked

Trusted Documents: "Disable Trusted Documents" is not checked

Add-ins: none of the three is checked

Macro settings:
1. of the four Macro Settings buttons,
only "Enable all macros (not recommended...)" is clicked on
2. the one Developer Macro Setting "Trust access to the VBA project object model" is not checked

Thank you again.


Ziggy1
Properties
With the Personal.XLSa open and active, I followed, from the website offered, the directions for my non-Classic menu of Office 2010, that is
File > Info [default]
I then discovered a dropdown menu under the heading Properties > Advanced Properties
I found a "General" tab in that view.
1. The location shown there is as confirmed previously correct for Excel 2010
C:\Users\Tacit\AppData\Roaming\Microsoft\Excel\XLSTAR[...
{the end of the pathway is cut off in mid-R by the right edge of the display frame}
2. The "Hidden" box is unchecked and greyed out

Thank you again.
Zack Barresse's Avatar
Computer Specs
Member with 5,434 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
29-Apr-2012, 08:24 PM #14
Okay, so the file is in fact opening with Excel. That is good. Now what is broken exactly?
Ziggy1's Avatar
Ziggy1 Ziggy1 is offline   Ziggy1 has a birthday soon! Ziggy1 has a Profile Picture
Computer Specs
Member with 2,525 posts.
 
Join Date: Jun 2002
Location: Ontario,Canada
Experience: Advanced
29-Apr-2012, 11:02 PM #15
when you have the properties window open, and on the general Tab > Location....

put your cursor in the field, press "HOME" key once to make sure your cursor is all the way infront of C drive, then press CTRL + SHIFT together and then Press END to highlight the entire path... then CTRL + C to copy

open a New Windows folder and paste this to the Address line at top and press Enter.... the file you see here is the file that opens with Excel

It is not clear to me ( or Zack) of your exact issue, but I would remove the file and then either record a new Macro in the personal so that Excel will recreate a new Personal.xlsb (not xlsa) file, or if you have a copy of one that you want to load then move it to this folder.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
excel 2010, not open, personal macro workbook, personal.xlsb, xlsa

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑