Workbook going bad?

  • Thread starter Thread starter Linda RQ
  • Start date Start date
L

Linda RQ

Hi Everyone,

Using Excel 2003 at the moment. We have an excel workbook that was probably
started in excel 97 or 2000. It is 36,560kb in size and has 110 worksheets.
The file is our competency check offs. Some of these include pictures which
were not compressed so that increases the file size.

We are beginning to have problems with it. It takes a long time to open up
at times. Different problems occur at different times and on different PCs.
Not sure if this has any relation but....I work for a large hospital and we
have Windows 2003 server, just upgraded our intranet to Sharepoint. One of
the errors that comes up is out of diskspace then the PC freezes and I am
asked to debug something and I finally have to ctrl+alt+del my way out. The
IS network person said it was a system problem. I am not sure he is correct
because if I log on to another PC and open the file it works, so I am
thinking it's PC related but the size of the file worries me too.

Is there a way to split this file or is that an ok file size for a workbook?

Thanks,
Linda
 
36meg is large..........I've used 20meg successfully
I've never seen a workbook with 110 sheets.........I can't imagine the
organization......
The error message says "out of diskspace"........is it?
Do you have plenty of RAM?
Do you have trouble with any other Excel files on that computer........
You say the file works ok on another computer......this would indicate the
file is not at fault.
Do you have the same trouble if you download the file to your Hard Disk and
run it from there?

Answers please....
Vaya con Dios,
Chuck, CABGx3
 
CLR said:
36meg is large..........I've used 20meg successfully
I've never seen a workbook with 110 sheets.........I can't imagine the
organization......
Click to expand...
It is basically a glorified document with each step in the procedure in a
row with borders to help with readability. Each competency is a worksheet.
They like it because they can find the sheets they need easily by searching
the tabs at the bottom. They use to scroll foever until I read on this
group about R clicking on the record selectors to get it in ABC order and
shown as a list so I am already a hero!
The error message says "out of diskspace"........is it?
Click to expand...

No, it is a network drive that is in the terabyte range.
Do you have plenty of RAM?
Click to expand...

Hmm...maybe not. I'll check tomorrow when I go in to work.
Do you have trouble with any other Excel files on that computer........
Click to expand...

No but they are not that large.
You say the file works ok on another computer......this would indicate the
file is not at fault.
Click to expand...

That's what I thought but still think this file is a corruption waiting to
happen.
Do you have the same trouble if you download the file to your Hard Disk
and
run it from there?
Click to expand...

We can't download anything to our hard disk. Everything is on a server in
another city for security purposes.


Is there a way in excel to make an index and perhaps have subsheets like you
can with word and the subdocument feature?


Congrats on surviving your CABG x 3!
 
When you say 110 worksheets, it suggests poor design.
Maybe formatting unnecessary cells. ctrl+end to see where cursor goes.
etc.
 
Would you create a separate file for each sheet? Is there a way to create
an index and hyperlinks to them then? I am just brainstorming here, I tend
to work in word and access mostly.

Linda
 
Probably design the program so that only ONE sheet is necessary with data
imported/exported as needed.????
 
Don't forget to check the RAM on your computer.

The fact that you are working an Excel file off a network can present
problems. Once I worked for a small company where I did that and they had
frequent interruptions to the network.....and each time, if I had an Excel
file open, it would freeze ad I would not be able to save, nor even
SaveAs.....had to crash out and start over....consequently, I learned to save
very frequently......the fact that your file is located even in another city
even compounds that problem.....if you could, even for a short period, do a
test by downloading that file and opening it from our own HardDrive and did
not experience the problems, it would clearly indicate that the problem
existed somewhere in the network, other than your computer and other than the
file, (but being able to run the file on another computer clears the file of
problems). You MUST be able to isolate the problem somehow in order to find
the solution.

You say that you do not have troubles with other files, "but they are not
that large"....could be this examplifies the network hit problem I was
describing...bigger file takes longer to load and save and is more
subsecptible to the problem.

As for youyr file design itself.....yes, you could make a MainMenu sheet and
have links to each of the other 110 sheets and hide them so the TAB bar is
not aclutter. Using macros, you could also break the sheets up into groups,
having all hidden until you fire a certain master group macro that would
unhide the related sheets only. For instance, you could have five users, and
a MainMenu with only their five names on it....whenever you press the MARY
button, then only Mary's sheets appear in the TAB row, etc......I created a
Training Matrix workbook for one client that has about 35 sheets, all hidden
and activated from a MainMenu sheet. Each sheet has about 1000 VLOOKUP
formulas on it, but they are not needed unless the sheet is visible and being
worked on.....so, I use Event Macros to delete all the VLOOKUP's when the
sheet is closed and hidden, and re-populate them when it is opened.
Something like that may help with your application.

You said:
Congrats on surviving your CABG x 3!
Click to expand...
Thanks, eight years and counting......

Vaya con Dios,
Chuck, CABGx3
 
Back
Top