Excel personal.xls file

January 21, 2010 at 16:19:38
Specs: Windows XP

Hi all,

I'm trying to make a macro (creates dynamic named range based on sheet name) available to all Excel workbooks.

Microsoft helped me do this:
Deploy your Excel macros from a central file

It suggested creating a "personal.xls" file in my XLStart folder. This file opens, hidden, everytime I open a new Excel workbook.

However, when I open an existing workbook, the personal.xls file doesn't open.

I'm fussy, and I want my macro available regardless of how Excel opens.

Any ideas?


See More: Excel personal.xls file

Report •


#1
January 21, 2010 at 16:58:49

It's hard to diagnose your problem from afar, but the personal.xls file should open whenever Excel opens, regardless of whether you open the application directly or open an existing file.

You are not being "fussy" becasue that's how it should work.

Don't take this the wrong way, but how do you know it's not opening?

If you open an existing file and go to Window...Unhide, personal.xls should show up there if it's open. Have you looked there?


Report •

#2
January 21, 2010 at 17:08:33

Another thing...

Under Help...About Microsoft Office Excel, click the Disabled Items buttons and see if it says anything there.


Report •

#3
January 21, 2010 at 17:24:15

Thanks Derby.

"... how do you know it's not opening?"
When I open up an existing file:
1) there are no macros. (When I go to Tools--Macro--Macros..., no macro come up, regardless of what I select in the "Macros in:" drop-down box.)
2) there are no hidden workbooks (When I go to Window in the file menu, Unhide... is greyed out.)

When I open a new file (e.g. Start--Programs--Office--Excel), personal.xls opens as a hidden workbook and it's macros are available via Tools--Macro--Macros... .

I've tried opening existing files from Local Disk (C:/) and from the network. Same issue.

Disabled Items
I checked the Disabled Items list. There are 3 entries, but none called "Document: personal.xls".

Am I missing something?


Report •

Related Solutions

#4
January 22, 2010 at 05:23:12

I'm looking into this, but I'd like you try 2 things:

1 - Do a Windows Search on xlstart. I've seen machines with more than one path to an xlstart folder and I wonder if this is an issue with you.

2 - Customize a tool bar in any Excel file and save it. For example:

Tools...Customize...Commands tab...Macros

Drag the Smiley Face up to a toolbar and drop it there.

If you haven't done anything like that before, this will create a .xlb file which stores the modified toolbars.

Now open Excel both ways - via the app and via an existing file - to see if the Smiley Face is present both times.


Report •

#5
January 23, 2010 at 17:27:47

Have a look at your file associations and actions associated with opening the XLS file.

- My Computer - Tools - Folder options - File Types
- Scroll down to XLS
- Click the Advanced button
- Double click Open

These are my settings:

Applications used to perform action

"C:\Program Files\Microsoft Office\Office10\EXCEL.EXE" /e

Use DDE - Checked

DDE Message

[open("%1")]


Report •

#6
January 26, 2010 at 15:49:16

I like your style, Derby!

1 - I found a number of XLSTART folders.
The one I put the personal.xls file in is "C:\Program Files\Microsoft Office\OFFICE11\XLSTART".
Another folder that stands out is "C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART". I copied the personal.xls file to this folder but personal.xls still didn't open with an existing file.
Other XLSTART folders belong to other users on my computer. All were under "C:\Documents and Settings\...".

2 - I'd already tried the toolbar... I created the Smiley Face button on a new toolbar and assigned to it the macro in the personal.xls file. The Smiley Face appears when I open Excel from the Start menu, however doesn't appear when I open an existing file.

3 (other post) - I checked the file associations and actions associated with opening the file; they match your settings including the "/e" at the end (except my folder is ...\OFFICE11\... where yours is ...\Office10\... .
DDE box is checked and DDE message is identical.

AutoRecover Save Location
Probably irrelevent, but I changed my AutoRecover save location from the default (to a folder in C:\).

FYI
I've tested the personal.xls file on a collegues computer and it opened with an existing file.
Excel should be set up in the same way on both our computers. This means my issue is likely caused by something I've changed in Excel's settings.

I just have to identify what it was...


Report •

#7
January 26, 2010 at 16:25:33

I don't know of any setting within Excel that would change how it deals with a new workbook vs. and existing workbook when it comes to the personal.xls file.

It's the application itself that goes looking for the personal.xls file, not the file you are trying to open.

The only other suggestion I have is to try this workaround:

1 - Create a folder and put only the personal.xls file in it.
2- Under Tools...Options...General tab... enter the path to the file (without the file name) in the field next to "At startup, open all files in:"

You may need to remove the file from the XLSTART folder so Excel doesn't try to open it twice, although that might tell us something if it does.

I've never tried this method of opening personal.xls (and I'm not going to mess with my machine!) but it might just work.


Report •

#8
January 26, 2010 at 23:15:26

I tried the "At Startup, open all files in:" field, but the problem presists.

However, while fiddling, I have managed to make the customised button stay in my toolbar; even when I open an existing file. This means that even though personal.xls doesn't open when Excel starts, it opens when I click the custom button (I chose the 8-ball FYI).

I played around with the excel.xlb and excel11.xlb files in "C:\Documents and Settings\username\Application Data\Microsoft\Excel".
I backed them up, deleted them, open/closed Excel and copied them back in. I can't explain it, but it worked.

Registry solution
I Googled some more solutions and an EggHeadCafe thread suggested a solution that involved doing some registry stuff via the "Run..." menu, here (about half way down):
EggHeadCafe

I cannot use the Run... feature on my work PC, so I can't test this. But anyone who reads this thread may want to try it.

Thanks again DerbyDad!


Report •


Ask Question