[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 837
  • Last Modified:

Excel personal workbook not opening every time

The Excel personal workbook is not opening every time Excel is started.  Every 2 to 4 times I open an Excel workbook, the personal workbook will become unavailable.  The problem usually, but not always, is proceeded by my getting the following message when closing an Excel workbook: "Personal.xlsb is locked for editing" by me.  The personal workbook will not load after that when opening other Excel workbooks.

To see why Excel might consider the personal workbook to be locked for editing I started the task manager and noticed that while the Excel application was not listed as an active application, the task manager listed the excel.exe process as active on the processes tab.  So even though I had closed Excel the process was still running.  If I force the process to close I am able to access the personal workbook the next time I open an Excel file.  But the problem appears again after I close and open Excel workbooks a couple of times.

Any ideas on what might be causing this problem and how to fix it?
0
marknlynn3
Asked:
marknlynn3
  • 4
  • 4
  • 4
  • +1
4 Solutions
 
dlmilleCommented:
You may have multiple instances of Excel being opened - I searched and this seems to be a common problem.

Try this solution and see if improves your expected performance:

http://us.generation-nt.com/personal-xlsb-always-locked-help-163788541.html

Dave
0
 
Rory ArchibaldCommented:
It sounds from what you describe as though you have an invisible instance of excel being left running? If so, I would disable all addins and test. If that seems to work ok, then add the addins back one by one until the error recurs.
You can also set the isaddin property of personal.xlsb to true so you can load multiple instances, but your macros will not then appear in the macros list. (I do this but I run all my code from ribbon buttons or shortcuts -or the VBE-, rather than the dialog)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
marknlynn3Author Commented:
Disabling multiple instances of Excel and disabling addins did not help.  Also, the personal.xlsb file was only found in one location, C:\Users\Mark\AppData\Roaming\Microsoft\Excel\XLSTART, so I don't think that is the problem either.  I could try reinstalling Excel, but I would prefer to avoid that if possible.

Unless someone can think of something else to try, I think I'll try converting the personal workbook to an add-in.  Could someone provide a little more detailed description of how to do this?  I looked at the properties for Module 1 of the personal workbook, but did not see any isaddin property to modify.
0
 
dlmilleCommented:
It could be as simple as saving as an addin. .xla or .xlam for 2007 and later versions

I'm not sure there's anything else to be concerned with is your personal wkb doesn't do menus or have open/close related events where you would want the behavior to be different
0
 
Rory ArchibaldCommented:
Look at the properties of the ThisWorkbook object in the personal workbook for the IsAddin property
0
 
dlmilleCommented:
You have to do more than just change the IsAddin property (I think).  Will addin files startup automaticallly if they're in the startup directory?  Secondly, the file needs to be renamed/saved as an add-in (otherwise, its still a .xlsb file) and by doing that, doesn't the IsAddin property get changed by Excel automatically?

Dave
0
 
Rory ArchibaldCommented:
No you don't. It's how I run mine. Any file in a startup directory will open automatically. (it will not appear in the addins list though unless you install it properly)
0
 
marknlynn3Author Commented:
I set up an add-in to hold my macros.  But I'm still having some problems.  Sometimes when I try to close the workbook, but leave the Excel application open, it is not possible to close the workbook and the ribbon becomes inaccessible.  This happens even when the personal workbook is removed from the xlstart folder and all add-ins are disabled.  I'm thinking the problem may have something to do with the  data files our data entry vendor sent us.  The files are currently in .xls format.  I think I'll try saving the workbooks in .htm format and then opening them again and saving them in.xlsx format to help clean up any problems, unless someone knows a better way.
0
 
dlmilleCommented:
I'm not sure, either.  What timing!  I just wrote an article/utility for Excel version conversion, try this tool: http:/A_8269.html = vote "Yes" if helpful! :) - and award this post a couple points to credit the article.... lol

If your personal.xlsb or addin didn't have any menus, beforeClose events, the work you've been doing to repair problems with it should not be causing these problems you're having.

If the data files don't either, then converting them to .xlsx should not have effect (promoting my article notwithstanding, lol).

You say you've disabled all addins, and your personal workbook is not in the start folder, and thus not running...  This is puzzling, if there are no macros in the .XLS files, either...

Let us know what you find.  Maybe rorya has some input as well (no doubt :) as I'm wading in the mud with you on this one - puzzling...

Dave
0
 
Rory ArchibaldCommented:
Do you use the preview pane in windows explorer?
0
 
marknlynn3Author Commented:
It looks like the preview pane being enabled was the problem.  I recall being annoyed with the pane yesterday because it kept expanding to cover up information I wanted to look at like date modified and file size, so it must have been enabled recently as I haven't had this problem in the past.  Anyway I disabled the preview pane and was able to use the macros in my personal workbook on ten files.  Previously I would have lost access to the personal workbook after processing and saving 2 to 4 workbooks.  So it looks like the problem is resolved.  Great detective work.
0
 
marknlynn3Author Commented:
Thanks much for helping to resolve this issue.  It's always a pleasure to work with knowledgeable people to solve a problem.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now