Why Would a File Grow?

Instead of having to bump up the complete file every time new data is added the excel grabs a whole block with lots of empty space to work with. Some of the space is released again on backup.

With more than one person working it is also better to have separate storage areas for each operator. That is to prevent complicated partial record locking.

The reason for the spreadsheet is to track some tooling we use, the tooling is used on many different machines and therefor I need to be able to track the tooling from machine to machine.

I know I can track from tab to tab, but can I track for workbook to workbook? Will that still work when one of the workbooks are closed?

"I know I can track from tab to tab, but can I track for workbook to workbook? Will that still work when one of the workbooks are closed?"

Sure. The data will not be updated until the target workbook is opened, but the data will be refreshed (after a prompt) whenever it is opened.

So the data is not real time, and I couldn't trust that the other workbook was open or not.

So, if I'm looking to see how many parts were added to cutter, and the cutter was used in two different workbooks, then I'd need both books open at the same time to know for sure how many parts that cutter ran ???

I'm going to test this..

"So the data is not real time, and I couldn't trust that the other workbook was open or not."

Correct, the data you see in Workbook A, Sheet 1 (target), even though linked to Workbook B, Sheet 1, Cell XX, is only real time for the second following a tap of the 'Refresh Data' icon at your workstation. This technique is sort of difficult with several data entry people; before closing a file, the last thing to be done is REFRESH DATA. Failure to do this housekeeping chore can result in the linked books and sheets not being updated.

The more I think about it, it would be difficult to trust your displayed data. Ya just never know, even with consciencious operators, sometimes things happen.

You might have enabled the track changes option in your file!!

Turned that option off a few days ago, the problem persists...

Out of curiousity, what happens if you saveas a new file name or saveas the same file name and overwrite it.

I tried that. Funny thing, you see if I take the large file, it stays large, no matter what I do with it..

So, here's what I've been doing. I go to the floor and find the 'not responding' files, mostly 2 of them, then I rename them something, and save them.

then I close them, return to my desk and pour myself a cup of joe. Then I open all the renamed files along with my 'back up'. Then I put all the data from the floor into my 'back up'. then I go into the network and delete all the 'big' files. Poof gone.

Then I rename my 'back up' and load it to the network, so it works again on the floor.

If I could only keep the file size the same as my back up, it would be fine... but it triples in only a few hours.... why???

perhaps a custom vb app? let me know.

as the cause? or as a fix?

Strange thing happened last weekend... the file did NOT grow. It was used as normal as we are a 24/7 operation and when I came in this morning, the file grew about 3K, as I would expect. Last week it grew by 7M in a few hours... what gives??

that is strange...

and yes, I was proposing to write an app for you, matching your current interface and structure, or improving on them, and eliminate the saving issues altogether. but if not, no problemo. if you do go with the access database, you don't have to save. its automatic. I'm no access expert though.

If your sheet is largely just data, then try saving as 'csv' (comma delimited format) and then close and open, start a brand new sheet, and import the csv file.. save, and check the size.

chris

Thanks, I'll give that a try.

This is just a guess, but I know other software vendors like Solidworks do it. They save the 'autosave' data inside the same file... so try turning off your autosave, or make it a larger number of minutes. It is default to like 10 minutes, and so if you walk away from the machine with the file open for 31 minutes it could autosave 3 times. let me know.

Chris

I agree... we are on the same track. Save as blows away the autosave info.

Chris

well put.

Sorry, your GA's were premature... I found the exact opposite to be the truth, when I had the option turned off, save only when saved, option was on, the file grew to the largest I've ever seen it, (over 32Mb) within 3 hours.

When I took the autosave from every 10 min, to every 5 min. it now takes 8 to 12 hours to grow from 7Mb to 12mb, but then I shrink it back.

It looks like the more I save it, the smaller the file remains. It's as if there is less to remember from the last save...

Any other ideas?

GA from me. This file growth stuff for Excel and Word is old stuff. Best solution for a highly active application like this tool tracking is to take it to an Access database. There's a bit more leeway for management and Access tables are basically Excel workbooks anyway.

Hooker

That has been suggested, and I'm looking at it. Big BUT is that I want one operator to see what the last operator put in, but setting it up like data entry will not work for me. I'm not trying to fill in tables. I'm trying to get information from one operator to another and from machine to machine..

So it needs to have the spreadsheet 'look and feel'..

Thanks for the suggestion, I'll let you know how it goes.

Perhaps I'm misunderstanding your app. You did say in your original post that the operators add X amount of data per shift. That sounds like data entry to me.

Anyway, the tables for inputting info could be made to look very much like your spreadsheet, you can turn on file locking so no two people are stepping on each other while entering data, and you can have up to 10 concurrent users in the database looking up info or updating data. And, the database can be available from anywhere on the network you choose to install a shortcut to it.

Just a thought,

Hooker

yea, they have to put data in, but it's as much or more for the shifts to follow then it is for that operator. example is he logs how many parts he did on his shift, the spreadsheet tells him how many are complete on the order, the next operator needs to know that, it's not tracked anywhere else. So shifts to follow need to see what he did, it also tracks the S/N the guy assigned.

there are also hyperlinks to all his set up sheets and work instructions, by part number...

So, it works great, if I can only keep the file size down so it will save and open without locking up...

Good answer. Using Excel for this purpose is like using a screwdriver as a chisel. Everyone uses Excel because it's what they know. Access or any other multi-user database app is the solution to this problem. It comes with "plenty" of data security and real-time merge (if not display) infrastructure.

As for the mysterious growth, my vote is for the expanding by chunks theory. Instead of adding 1KB to the file, Excel grabs 12MB because it can and won't have to expand again for a while.

I can't give you the technical reason why, but what you see is "normal". I had a simple photo database/view spreadsheet set up (that referenced a separate folder containing 4000 jpg images) and I pasted and deleted individual images as determined by a macro search into and out of the single Excel "viewing" page. The file grew to something like like 10M when it contained no photographs and about 400K of macro and index "data".

All the simple things like deleting spare lines. saving, copying to a new file name, renaming (is there a compacting) etc did nil, nothing, zilch. I knew that the real size could not possible be 10M and persisted.

In in the end I did something like open a new file and paste and copy just the cells into the new document, and probably imported the considerable body of macros across as a separate exercise, and by that mechanism I got the file size down to around 400K.

I can only assume that Excel keeps on logging some sort of history or index (maybe for recovery) that is is either not needed or no longer relevant in the background - and that it is not able to be seen or deleted by a standard/documented approach.

I was using Excel 2000, but I suspect this phenomena is likely to exist in other versions.

maybe some one said this, but based on what you say you're tracking, why not a date base instead of a spreadsheet. Can set up form or forms for your shift users, too.

The default file cluster size, when a hard drive is initially formatted prior to the installation of an operating system, is usually in blocks of 512kb. When a file like a picture for example is just under the default cluster size, the remaining free space within that cluster is called file slack space. Clusters that are 100% unused are called free space. When a file is marked for deletion and moved from the garbage to complete the deletion process, the entire block of 512kb is tagged as available and is then recognized as open hard drive space again. It can then eventually be over written as needed by the operating system. Up until the operating system reuses this recycled block of storage area, the old file that used to reside there before before deletion is still there however the identifiers originally associated with that file when it was in working order are removed in a manner that it is not recognized as the format used in that particular operating system leaving it basically ignored as free space. You can probably measure your file size changes and they will be proportionally equivalent to multiples of 512kb. Hard drive cleaners like Webroot's Window washer offer cleaning of both file slack and free space to make best use of the available slack in multiple files not using the complete 512kb per cluster. There will always be some slack. Some Programs or applications may have the file size effeciency built into how they store complete file sets. Basically these applications are doing the slack file maintenence instead of a 3rd party program like Window Washer. Windows basically does this when defragging also. Don't worry, your network is not becoming self aware lol. Hope this helps

Hi all,

thanks for all your help, it was worth it to me. This project and this spreadsheet has become extremely important to my company. My boss showed to higher up's and they wanted it to go global. As you can well imagine, I needed it to work.

So here's what happened. The spreadsheet crashed (again) and as always, all my formats were wiped out and a few of my formulas. But this time, instead of deleting the file, I read the recovery log that Excel created.

It listed a bunch of things it "corrected" and said the file was safe to use. ummmm....

So I reformatted the lost information, put in the lost formulas and checked the size.. 1.3M (not bad, it was well over 7M)

Now it's been running for over a week, and the current size is 1.34M.

Problem solved... thanks for the help, but it seems Excel knew of the problem and was trying to help me.

Laby