Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel 2013 "Can't complete this task with Available resources"

Posted on 2014-01-15
7
Medium Priority
?
13,656 Views
Last Modified: 2014-01-26
Hi,
Have got the following issue that I'm hoping someone can help with.

Current Setup
- User is using Windows XP laptop with 4GB RAM
- Has Office 2010 installed
- Runs a complicated Excel file that has a connection to an SQL Database and Pivot Tables
- When she refreshes data in it, it takes a long time (several minutes) but it eventually refreshes with no issues.

New Setup
- We are rolling out a new laptop with Windows 8.1 and Office 2013 (8GB RAM).
- When we attempt the same refresh of data in this file using Excel 2013 it fails with the error "Excel cannot complete this task with available resources.  Choose less data or close other applications.  Continue without Undo?".
- We can click "Ok" to this message but the worksheet doesn't refresh properly.

TESTING
- Have tried this file on three different computers running Windows 8.1 and Excel 2013 and they all fail with the same error (2 machines with 8GB RAM, one with 4GB).
- Have monitored memory during this on the 4GB RAM machine and it still had 1GB of available memory left when it failed.
- Have notice when it fails, that the info bar along the bottom of Excel always says: "Calculating Pivottable Report".  So possibly something to do with this?

- On 8GB machine that was failing, I uninstalled Office 2013 and installed 2010.  The file then updates fine with no issues.


So I believe this is an Excel 2013 issue.  Does anyone have any ideas on where I can start looking?  If anything I would imagine that the 2013 version should cope better with more data than the 2010 version?  Is there some limitation that needs to be removed for PivotTables maybe?

Thanks.
0
Comment
Question by:iknowsfa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 3

Expert Comment

by:Sreeram
ID: 39784739
Hi

This error occur when the Excel and windows is out of sync.

Solution:

Insert DoEvents in the code. were you feel code take more time to execute


Doevents  will make excel to wait till it sync with windows


Sample code:

   DoEvents
          ' Your code goes here

 DoEvents
0
 

Author Comment

by:iknowsfa
ID: 39786432
Hi,
I'm unsure where I would find this code to insert the statement you refer to.  there are no Macros in the file, it just seems to use Pivot Tables, and I'm not familiar with them.
Can you be more specific about how and where to put it?

Also seems a bit strange that Excel 2013 would "go out of sync" with Windows when Excel 2010 can run the file fine with no issues?  Or is this something you've seen before with this edition of Excel?

Thanks
0
 
LVL 3

Expert Comment

by:Sreeram
ID: 39787626
Hi

    Try this once .

Make zoom level of all the sheet in the workbook  as equal and check are you facing the same problem.

let me know the status of the solution so that i can give you an exact explaination for the problem.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:iknowsfa
ID: 39792639
Hi,
There is only one sheet in the file.  To test I changed to zoom level from 75% to 100% and then tried clicking the DATA > REFRESH ALL button again.  But unfortunately am still getting the same error message.
Will keep trying some other stuff to see if I can get it to work.  If you have any more suggestions they would be greatly appreciated.

Thanks.
0
 
LVL 3

Expert Comment

by:Sreeram
ID: 39793652
Hi,

    I too had the same problem. I was populating a dropdown from different tab sheet. when the zoom level is different in both the sheet  I faced this problem.

thanks
0
 

Accepted Solution

by:
iknowsfa earned 0 total points
ID: 39797947
Ok, I've managed to solve this myself.  Was a simple thing which I can't believe I hadn't checked earler.  The file was still saved as an old version .XLS file.  I converted to the new file format .XLSM, and it now runs fine with no errors.
0
 

Author Closing Comment

by:iknowsfa
ID: 39809832
Other suggestions didn't solve the issue.  But after my own testing managed to find the answer.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question