Out of memory with shared workbook (2000sr1a)

Topic

New Reply

I have a shared workbook on a network. I am trying to delete thousands of lines in one sheet, and every time I try and do so, I start receiving out of memory errors, then my display goes wobbly, and finally my machine restarts. Prior to this excel is using about 35 MB in task manager, and has generated over 3 million page faults. I am not running out of physical memory as I have 192MB, most of which is reported as free at the time. I’m running win2k pro sp2 on a PII 450 machine. The other apps running at the same time are my firewall and virus scanner, and that’s about it. Oddly, during this time, spoolsv.exe is using about 25% of CPU time, and I can’t think of what this has to do with deleting lines in a shared workbook.

So, how can I delete these lines without losing my change history? Is it possible to increase the resources allocated to an application? Would changing virtual memory settings help (not that I’ve ever seen where to do this in win2k)?

Another possible workaround would be to save a change history, unshare, delete and then reshare, but I’m not sure how to carry out step 1 of this plan.

I’m also rather disturbed to see this spontaneous reboot, and would be interested in knowing why that happens, but that’s an aside.

Thanks

Reply | Quote

Replies

It may be to do with automatic recalculation. Turn this off before you do the delete and on again afterwards.

HTH

Reply | Quote

Just some comments, since I haven’t got a solution other than unsharing.

XL isn’t too good at this sharing of workbooks. I’ve seen many people passing by with trouble with shared workbooks.
Access is -by far- better at reliably sharing information.

From the fact that you need to delete thousands of lines I conclude (maybe wrongly..) that your workbook contains lots of records. Data like that belongs in a database IMO.

Note that it is not necessary for all users to have Access on their system to be able to use the data. One can use Excel and ADO (or DAO) to get data from and update data into a database file.

Reply | Quote

Sadly, turning off recalculation didn’t help, and although this data does belong in Access, the spreadsheet began life as a simple sample tracking sheet and has grown. I started in Excel because everyone is comfortable with it and it’s difficult enough anyway to get people to bother logging things. Getting an Access db to the same level of useability requires a fair amount of work. I also don’t need the data to be shared as such, but I need to know who changed what from what and when. I could do this in Access, of course, but it would require setting up a workgroup information file and more coding.

There may be another way round this. Actually what I want to do is recover from the annoying Excel “feature” of deciding that all 65000 rows on a work sheet are active, rather than just the rows that contain data. I’ve never understood why this happens, but it’s a real pain if you’ve got a few hundred rows. Usually it’s easy to deal with, but I can’t find any way of sorting it out in a shared workbook.

Reply | Quote

I guess you’re a bit out of luck here. Removing the sharing option wipes out the change history. I guess all you can do is create a history sheet (Tools, track changes, Highlight changes, check “list changes on a new sheet”) and before saving or unsharing the workbook, make a copy of that sheet to a new workbook (it gets deleted when one saves or unshares the shared workbook). Then unshare and remove excess rows..

Reply | Quote

>. I guess all you can do is create a history sheet

Duh!

Thank you for pointing out my stupidity.

Reply | Quote

<>

Not at all! Just happened to stumble upon that option. In fact, I expected you te answer “I’ve already done that” .

Reply | Quote

Aaaaagh! Not so simple after all. When you create a changes sheet, and then remove that workbook from sharing, the sheet is deleted!!!!! You have to move it to another book before removing the share, rename it, and move it back when the book is unshared.

Reply | Quote

I said that in my post suggesting to create the list.

Reply | Quote

Hi Mark,

A couple of things you might try:
1. Change the change tracking so that it doesn’t track your changes while you’re deleting the rows (ie ‘Everyone but me’ in Excel ’97). Then restore the usual settings afterwards.
2. Use a macro to delete the empty rows – Excel may treat this differently.

As for the use of so many rows, are you sure that one of the users whith whom the workbook is shared hasn’t applied formatting over an extended range, and that there aren’t any formulae referencing cells that far down? Both of these could cause Excel to regard those extra rows as being in use.

HTH

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote