Results 1 to 5 of 5
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    error msg 'cannot complete task' (Excel 2003)

    Any clues what this message really means?
    "Excel cannot complete this task with available resources. Choose less data or close other applications."

    I have no other workbooks open, and other active things are Outlook and other background services that I can't get rid of. I did a search in MSKB but they only had one listing referring to shared workbooks and tracking changes neither of which pertains to me (#331863). It happens when I click on one of 5 list boxes I have in my workbook. These list boxes are on one sheet and pertain to 5 equipment lists (product name, price, quantity). The user clicks the item of interest and is prompted (via InputBox) for a quantity. After this is done, I get this error. It does lock up Excel for about 15 seconds but after that it works ok (until the next time).

    My PC may be at fault for other reasons though as I do have some weird things going on (too detailed to bog down this post) but I don't have another PC with xl2003 on it to try it out.

    Any ideas?

    Deb <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error msg 'cannot complete task' (Excel 2003)

    Thanks for the hints... on one of these links, http://support.microsoft.com/?kbid=313275 it has a 3 step workaround:

    1. Fill only the part of the range that you have to fill.
    2. Select only the last row or last two rows of the filled range, and then fill farther down the sheet.
    3. Repeat step 2 until you have filled the entire range that you have to fill.

    I'm not clear how they're suggesting that I fill a range. Can you decipher what they're asking me to do instead? <img src=/S/groan.gif border=0 alt=groan width=16 height=15>

    This DID give me an idea to do a test where I deleted one worksheet which contains 99% links to cells on other worksheets (for a total of 522 rows with these 4 cell references each, total 2088). This sheet is hidden and used to create an invoice. The code hides the rows that aren't needed in the invoice (the ones with non-zero quantities) and then it displays it in Preview Mode for the user to print or not. After I deleted this sheet, I haven't been able to duplicate the memory error. THAT IS GOOD, however, I really need that invoice sheet and creating it programmatically (on-the-fly without just hiding/showing the rows to be printed) is a huge effort.

    Since I did indeed create this sheet by using the mouse to fill down the 500+ rows/cols, I guess that action is what kicked Excel over the edge. Is that what this workaround is suggesting? Am I to just copy down less rows at a time, say 20,and then click on cell 21, and copy down another 20? Is that what they're offering as a workaround?

    Deb

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error msg 'cannot complete task' (Excel 2003)

    Yep, the suggestion is to to break up a large fill down into several steps, filling down only a limited number of rows in each step, say:
    Select the cells you need in row 1.
    Fill down to row 51.
    Select the cells you need in row 51.
    Fill down to row 101.
    Select the cells you need in row 101.
    ...

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error msg 'cannot complete task' (Excel 2003)

    This seems to do the trick!!! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> I ended up deleting all 2200 references (550+ rows with 3 cols each), saved, closed, re-opened the workbook. I then did the fill range on 15 at a time (15 rows, 3 cols), saving after each one (just being cautious), selecting the next row, then fill down again and it seems tosolve the problem of getting that resource message.

    It just shows how bad the memory management is in Office. Even between closing/opening a workbook, it still somehow retains the memory it used to complete the copy fill actions??? Very strange. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    If not for the Lounge I was going to duplicate this report via code and that would of taken a long, long time.

    Thnx, Deb <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

Posting Permissions

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