Excel VBA – PERSONAL.XLS Locked For Editing – Resolution

I’ve just been working on an Excel problem that requires one worksheet to be opened at a given time (using the Windows Task Scheduler) and a macro run.

However, when testing this I came across the “Personal.XLS locked for editing” message.

It seems that this is due to having more than one instance of Excel running at one time.

Dave Peterson at http://www.pcreview.co.uk/forums/thread-2831009.php has suggested a solution to this.

This involves marking the personal.xls file as read-only and this, indeed, seems to stop the error.

If you haven’t come across the personal.xls file before it’s a hidden workbook that opens when you start Excel. Any code in this workbook is available in all workbooks you have open in Excel.

On a Windows XP machine the file is usually located in the Excel startup folder at:

C:\Documents and Settings\[UserName]\Application Data\Microsoft\Excel\XLSTART

If you’re using Vista it’s probably in the folder at:

C:\Users\[UserName]\AppData\Roaming\Microsoft\Excel\XLSTART

where [UserName] is the user name you’re currently logged onto the machine as.

However, the file could be located elsewhere so use Search to find it if it’s not in the folders mentioned above.

Although making the file read-only solves this problem it does raise other issues if you often add code to personal.xls but is a useful workaround in my situation.

Advertisements

4 Responses to “Excel VBA – PERSONAL.XLS Locked For Editing – Resolution”


  1. 1 Stan October 15, 2010 at 3:04 pm

    Best and concise solution ever.
    Worked like a charm.

  2. 2 William August 26, 2011 at 2:35 pm

    Lovely, elegant solution.
    Thank you.

  3. 3 Lockie November 23, 2011 at 11:56 pm

    Worked perfectly, thanks!

  4. 4 gerdami (96) (@gerdami) January 19, 2012 at 1:48 pm

    Another workaround is to share your workbook (with yourself): Tools, Share Workbook, check the box next to “Allow changes by more than one user at the same time.”
    Caveat: the enclosed VBA project is unviewable shared mode.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Blog Stats

  • 222,048 hits

Counter

ClustrMap


%d bloggers like this: