CR4® - The Engineer's Place for News and Discussion®


Previous in Forum: IT Testing   Next in Forum: Sharing Printer
Close
Close
Close
10 comments
Guru
Engineering Fields - Manufacturing Engineering - New Member Hobbies - Target Shooting - New Member United States - Member - New Member Hobbies - Hunting - New Member

Join Date: Dec 2006
Location: Charlotte, NC USA
Posts: 751
Good Answers: 17

My Excel is Locking Up

02/08/2016 12:12 PM

I'm using Excel 2010 on Windows7. I've created a file with 34 tabs, each supporting a different machine in the shop. I've established hyperlinks that pop up for the operators if they put in a part number and work order number that supplies the operators part number specific set up instructions. I've use the header on each tab with hyperlinks to machine specific work instructions. I've been using this as a shared file on 18 different computers though out the shop, to not only deliver work instructions, but provides the operators a place to record the serial numbers of the parts they run. Because each tab is machine specific, no 2 operators need to be on the same tab at the same time.

Here's the problem, the file locks up. If I have 2 operators try to input data to the same field the file will lock up with an error asking one or both operators to accept their or the other input. Once that error window is cleared, the file is not longer "locked". I've been using this file for the past 4 years, I've had problems, I've had to archive the file twice, I've change it from .xls to .xlsb and that help increase the speed of opening and saving.

Lately, in the past 2 or 3 weeks, the file locks up without an error message anywhere I can find.

The error :

"This file is locked. Try the command later."

I've searched all 18 different computers and none of them has an error window I can clear to unlock the file. After letting it sit for 3 or 4 days without the file unlocking, I re-name the file, copy all the operators data from all 18 computers and start over. That normally fixes the problem for a few months, the last time I did that was Friday, today I came to work and the same thing happened, it's locked again.

Does anyone know a way I can keep this Excel file from locking up?

Thanks

Labyguy

__________________
Be careful of what you wish for .....
Register to Reply
Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.

Comments rated to be Good Answers:

These comments received enough positive ratings to make them "good answers".
Guru

Join Date: Mar 2007
Location: at the beach in Florida
Posts: 19288
Good Answers: 1129
#1

Re: My Excel is locking up

02/08/2016 12:50 PM

Have you tried the Offcat..? Maybe an upgrade is in order...

__________________
Life is like riding a bicycle. To keep your balance you must keep moving. A.E.
Register to Reply
Guru
Engineering Fields - Manufacturing Engineering - New Member Hobbies - Target Shooting - New Member United States - Member - New Member Hobbies - Hunting - New Member

Join Date: Dec 2006
Location: Charlotte, NC USA
Posts: 751
Good Answers: 17
#2
In reply to #1

Re: My Excel is locking up

02/08/2016 12:57 PM

Thanks, I sent that off to my IT department, I'm waiting to hear back if I'm allowed to try it. If so, I'm thinking I may need to do that to all 18 machines on the shop floor.

__________________
Be careful of what you wish for .....
Register to Reply
Guru

Join Date: Aug 2007
Location: Earth - I think.
Posts: 2028
Good Answers: 158
#3

Re: My Excel is locking up

02/08/2016 1:04 PM

"Because each tab is machine specific, no 2 operators need to be on the same tab at the same time.

If I have 2 operators try to input data to the same field the file will lock up"

If they are on different tabs, they cannot both be on the same field. Therefore, what you have is an operator training problem.

__________________
TANSTAAFL (If you don't know what that means, Google it - yourself)
Register to Reply
Guru
Engineering Fields - Manufacturing Engineering - New Member Hobbies - Target Shooting - New Member United States - Member - New Member Hobbies - Hunting - New Member

Join Date: Dec 2006
Location: Charlotte, NC USA
Posts: 751
Good Answers: 17
#4
In reply to #3

Re: My Excel is locking up

02/08/2016 2:02 PM

If I have 2 operators trying to input data to the same field, it's the easiest fix there is. I can clear that in 2 sec. and be on my way.

My question is why is it locking up when I can't find the reason anywhere, or better yet, is there a way to prevent it from locking up at all.

Thanks in advance...

__________________
Be careful of what you wish for .....
Register to Reply
2
Guru
Technical Fields - Project Managers & Project Engineers - New Member

Join Date: Aug 2006
Location: Midwestern United States
Posts: 830
Good Answers: 73
#5

Re: My Excel is Locking Up

02/08/2016 2:53 PM

"All your eggs in one basket" comes to mind here. What if you had a separate file for each machine, and then a master file that data-mined them into a master reference file - if that is your end desire. I'm just not a big fan of single file data management.

__________________
Reuters - Investigators found that the recent thread derailment in CR4 was caused by over-weight creatures of lore and request that membership DON'T FEED THE TROLLS.
Register to Reply Good Answer (Score 2)
Guru
Engineering Fields - Manufacturing Engineering - New Member Hobbies - Target Shooting - New Member United States - Member - New Member Hobbies - Hunting - New Member

Join Date: Dec 2006
Location: Charlotte, NC USA
Posts: 751
Good Answers: 17
#6
In reply to #5

Re: My Excel is Locking Up

02/08/2016 4:08 PM

That sounds like a great idea, I'll have to read up on how to do that.

Thanks

__________________
Be careful of what you wish for .....
Register to Reply
Guru

Join Date: Oct 2009
Posts: 1197
Good Answers: 23
#8
In reply to #6

Re: My Excel is Locking Up

02/09/2016 5:13 AM

I have solved a similar problem by having two Excel files open at once. For example, you have ThisMachineFile.xls and MasterFile.xls. Within the machine file you can refer to the master file using the syntax [MasterFile.xls]Sheet1!A2. The square brackets must enclose the full name of the file, including the suffix, the tab name is followed by the exclamation mark and the grid reference may be either a direct cell reference or a field name.
Any number of users can read from a single file, but it should be self-evident that the software will not allow two users to write to the same cell. In general I find that even two programs on the same computer will not write to the same open file.

Register to Reply
Guru

Join Date: Dec 2010
Location: North West England
Posts: 925
Good Answers: 116
#7

Re: My Excel is Locking Up

02/09/2016 4:22 AM

It could be that you have hit a memory limit and an overflow flag is locking up your system. I suspect this because quite a few of the maximum parameter settings in Excel are limited to 32767. That just happens to be the number of seconds in 3.79 days which could be when your system does a self reset. If this is the cause splitting the file will solve the problem.

Register to Reply
Guru

Join Date: Jul 2012
Location: Madison, WI.
Posts: 1036
Good Answers: 45
#9

Re: My Excel is Locking Up

02/09/2016 9:56 AM

Excel files are notoriously big. Excel is a memory hog. It is possible you have exceeded your machine or servers memory limits. Windows OS can easily run out of memory because it leaves the most used programs partially loaded. It does this because for some strange reason people seem to think they should be able to just click a button and the information appear. (reality, computers don't really have that ability without tons of RAM) As for Excel,

Usually if you try to open a spread sheet that is already open by someone else it will tell you so and only allow a copy to be opened until the current user closes the original out. Any entries made on the copy will be retained on a file clearly marked copy when saved and not in the original spread sheet. If you are not careful you will have numerous copies in your system. This creates confusion and reduces your memory even further if there are more than one copy open along with the original.

Have you been having mysterious data losses? "hey I reported that!" "well its not in the system." kind of things?

Are other things running slow?" Glitchy? Sticking?

Have you looked at the Resource monitor program to see how the system is running?

Control panel/performance information and tools/advanced tools/Resource monitor

Its a start.

__________________
Knowing is the end result of learning, not believing.
Register to Reply
Guru

Join Date: Feb 2012
Location: just died off here (now i may flicker back for a while)
Posts: 575
#10

Re: My Excel is Locking Up

04/07/2016 6:29 AM

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

use excel for interface not for interface and db (excel recalculates all dependent cells on changes = a waste of energy if not also time)

use something that formerly was MS Jet DB to handle db updates via SQL or alternate (just perhaps! to address the other* than user interface excel files (*update a bkp cpy.-s in cloud or other file server regularly))

e.c.

__________________
?Guru ?WT... -- i just got p-z of technical´s of this place some "long ago" . . . come on look at my picture . . . ? (Kan)G(a)u̶ru̶(oo)
Register to Reply
Register to Reply 10 comments
Interested in this topic? By joining CR4 you can "subscribe" to
this discussion and receive notification when new comments are added.

Comments rated to be Good Answers:

These comments received enough positive ratings to make them "good answers".
Copy to Clipboard

Users who posted comments:

ci139 (1); JavaHead (1); jhhassociates (1); Kilowatt0 (1); Labyguy (3); phph001 (1); rashavarek (1); SolarEagle (1)

Previous in Forum: IT Testing   Next in Forum: Sharing Printer

Advertisement