Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Out of memory with shared workbook (2000sr1a)

    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

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Out of memory with shared workbook (2000sr1a)

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

    HTH
    David Grugeon
    Brisbane Australia

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Out of memory with shared workbook (2000sr1a)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Out of memory with shared workbook (2000sr1a)

    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.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Out of memory with shared workbook (2000sr1a)

    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..
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Out of memory with shared workbook (2000sr1a)

    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
    [MS MVP - Word]

  7. #7
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Out of memory with shared workbook (2000sr1a)

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

    Duh!

    Thank you for pointing out my stupidity.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Out of memory with shared workbook (2000sr1a)

    <<Thank you for pointing out my stupidity>>

    Not at all! Just happened to stumble upon that option. In fact, I expected you te answer "I've already done that" <smile>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Out of memory with shared workbook (2000sr1a)

    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.

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Out of memory with shared workbook (2000sr1a)

    I said that in my post suggesting to create the list.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •