Excel cannot complete this task......

[Guest]

Hi, I am using Excel 2002 and would appreciate help on a couple of problems /
issues / concerns / ......
To begin with, I created a spreadsheet with links to about 50 items in
another spreadsheet (using Special Paste feature) and when I try and Save and
/ or Exit, I usually get an error message advising "Excel cannot complete
this task with available resources. Choose less data or close other
applications. My question is, this error message does not appear all the
time, may 4 out of 5 times that I access this file, and I understand that
Excel is advising me of a resource problem but I have 1G RAM so I don't quite
understand why I am having a problem Saving / Closing the spreadsheet?
Any thoughts / comments / suggestions would be appreciated.
The other question is very minor but I thought that I would mention it in
case that it might affect my Save / close problem above.
When I click on the Excel icon (I should also mention that Excel is part of
Office 2002), a Blank spreadsheet titled Book 1 opens.
I would rather start Excel with no spreadsheet since I usually use "old"
spreadsheets that I created and don't have the need for a New Spreadsheet
very often (so I need to close the Book1 spreadsheet to continue).
Anyway, I would appreciate any help that you can offer concerning the
Resource problem stated above.
Thanks,
Bob

 

[Anne Troy]

Bob: It could just mean you need your hard drive cleaned, which can cause
misreporting and problems like yours:
http://www.officearticles.com/misc/how_to_clean_up_your_hard_drive.htm
It could mean that your file is or is becoming corrupt, and may need an
overhaul:
http://www.officearticles.com/excel/troubleshoot_your_workbook_in_microsoft_excel.htm
As for opening Excel without a workbook, it's not designed to open without
one. You could easily emulate it by opening Excel, hide book1 and close
Excel. When it says "Do you want to save...?" say yes to save the hiding of
book1. Otherwise, it MAY require code to open Excel without that workbook.
Is there some reason you don't just double-click one of your file icons to
open Excel?
****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com
****************************

 

[Guest]

Thank you for your help Anne, I would have never thought of a HD problem
which should not be the situation since I Partitioned my drives and have
about 70% free in the HD that I store my data.
Anyway, I appreciate the link and will follow directions.
As far as opening Ecel, I usually click on the Excel icon on the MSOffice
Toolbar which automatically opens Wook Book1 and does not "allow" me to
select" the actual spreadsheet that I plan to work with.
Thank you again for your help Anne,
Bob

 

[Guest]

Hi again, I think I was a little too quick to thank you for your help becuase
I ran the instructions on the links provided and ran into ANOTHER problem. I
was able to identify the Tmp.Chk files but when I tried to delete them I was
presented with a "Nothing to Do" error message so I tried to manually (find
and) delete them but discovered that they were Read Only files that I was
unable to delete. BTW, they were Word files although I am able to open and
close Word files with no problem but am having trouble closing (saving) Excel
files.
Your further instructions would be appreciated.
Thanks,
Bob

 

[AmendConstitution_ArnoldForPresident]

Anne, thank you for your link. You are practical and pragmatic there.

Boblink I have virtually the identical affliction. It's been a
problem for a long time, and cost countless money and loss of lives
while MS has waxed impotently about it. I hope the Excel development
team sleeps well at night while this problem has existed for years.

In my case the crash happens at different times. I can open the file
and run the macro and have it crash the app. when 25% done; I can then
do EXACTLY the same thing and have it make it 55% of the way through.
I also have 1 Gig memory, which I am certain is not stressed outside
of Excel; and the .XLS is a single MEG. I thank the people who offer
well-meant responses of "you're short on memory. Try closing
Outlook." That's simply not the issue here.

Successive *pastes* appears to be the crux of the problem. One
tactical response is to add
Application.CutCopyMode = False
after pastes. This may help others; it didn't solve things for me,
but it apparently transformed the crashes to new mutations and new
timings. In the many hours I've bled researching this, over and over
I hear that pastes are flaky.

I have had vomitously choppy "success" from subdivide/conquer. To
illustrate: I have 1650 total values to calculate. 550 are displayed
on each page. There are 11 columns of 50 values each. If I divide
the problem into 33 tasks and manually run the macro 33 times, maybe 7
times out of 10 I can make it to the goal line, whereas I crash 100%
of the time if I run a single loop. Imagine the conversations I've
had with the company CEO. He says, "What do you mean, you simpleton?
You can't write a loop? Isn't that what I learned to do in BASIC 35
years ago??"

Divide and conquer is the only spitball I've found against this
Goliath.

 

[matt]

Anne, thank you for your link. You are practical and pragmatic there.

Boblink I have virtually the identical affliction. It's been a
problem for a long time, and cost countless money and loss of lives
while MS has waxed impotently about it. I hope the Excel development
team sleeps well at night while this problem has existed for years.

In my case the crash happens at different times. I can open the file
and run the macro and have it crash the app. when 25% done; I can then
do EXACTLY the same thing and have it make it 55% of the way through.
I also have 1 Gig memory, which I am certain is not stressed outside
of Excel; and the .XLS is a single MEG. I thank the people who offer
well-meant responses of "you're short on memory. Try closing
Outlook." That's simply not the issue here.

Successive *pastes* appears to be the crux of the problem. One
tactical response is to add
Application.CutCopyMode = False
after pastes. This may help others; it didn't solve things for me,
but it apparently transformed the crashes to new mutations and new
timings. In the many hours I've bled researching this, over and over
I hear that pastes are flaky.

I have had vomitously choppy "success" from subdivide/conquer. To
illustrate: I have 1650 total values to calculate. 550 are displayed
on each page. There are 11 columns of 50 values each. If I divide
the problem into 33 tasks and manually run the macro 33 times, maybe 7
times out of 10 I can make it to the goal line, whereas I crash 100%
of the time if I run a single loop. Imagine the conversations I've
had with the company CEO. He says, "What do you mean, you simpleton?
You can't write a loop? Isn't that what I learned to do in BASIC 35
years ago??"

Divide and conquer is the only spitball I've found against this
Goliath.

I'm a little interested in what is going on here because I almost
don't believe you when you infer that "Excel can't do it." For
example, I have a macro that calculates well over 2,500 formulas and
keeps track of over another 60,000 other formulas (composed of 24
columns and 2,500 rows) which are all created via a macro. When I run
my macro, I don't crash.

I'm just trying to say that I'm interested in your problem. Do you
have any posts of your macro?

Matt

 

[AmendConstitution_ArnoldForPresident]

The code is a little unwieldy to completely post, but here's a taste
If gbDirtySheet Then cmdDoAccessQuery_Click
Application.Goto Reference:="SummaryDur1to25"
Selection.Copy
Application.Goto Reference:=sResultStartPoint
ActiveCell.Offset(0, i - 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False 'hopefully corrects automation
bug in Excel
Application.Goto Reference:="SummaryDur26to50"
Selection.Copy
Application.Goto Reference:=sResultStartPoint
ActiveCell.Offset(12 * 25, i - 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False 'hopefully corrects automation
bug in Excel

This never makes it through the 550 times I request it.

Sure, it's easy to write a billion operations, and it runs sweet.
Sub foo()
Dim i As Long
For i = 1 To 1000000000: If i Mod 10000000 = 0 Then Debug.Print i
Next
End Sub
But I don't think capacity or limits is the issue here...

 

[Guest]

We are also experiencing the same error message, but I think a slightly
different problem. We're in a corporate environment with Excel 2003 and
we're creating PivotTables from a SQL database that houses our time worked.
The number of records isn't large enough to exceed Excels max, memory doesn't
seem to be a problem, and the problem exists on newly created files as well
as old files, so corruption shouldn't come in to play.

The data displays. and all of the pivottable functionality works, save one
drop down where the user can limit or expand the time frame covered by the
data. When attempting to access the dropdown, the error message "Excel
cannot complete this task with available resources. Choose less data or
close other applications". We've tried limiting the data coming back from
the query to relatively short periods of time (and a smaller dataset) wth no
change.

We've tried this on several different computers, including a Citrix server
with 4 Gbs of RAM and dual daul-core 2GHz processors. The machine originally
exhibiting the problem has 2Gbs of RAM, a dual-core 2GHz CPU and reports that
a pivottable exhibiting the error is only using 2.3Mbs of memory, while Task
Manager reports Excel is using 30.79Mb total.

The older reports which now have the problem worked in the past, and the
problem is present on new PivotTables as well.

I've exhausted my resources in dealing with this. Any help is appreciated.