Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Excel cannot complete this task with available resources. Choose less data

    From time to time after opening up a few workbooks and running macros and then trying to opening up a new workbook directly from Excel or from an Outlok attachment, I get the following message

    "Excel cannot complete this task with available resources. Choose less data or close other applications"

    My IT Dept even gave me a new PC and loaded all the data on this, but I still get this message when opening up a new workbook after running macros on several workbooks. There is no specific macro that is causing this

    I am using Windows 7 (32 bit version) 4 GB of Ram and Office 2010

    It would be appreciated if someone could assist me in resolving this problem

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    It sounds like a "memory leak" issue. You run code and it does clear memory well enough and "XL's memory manager" believes the memory is full. Does rebooting help after running the code and then opening the new workbook? Also have you tried looking at some of the comments/solutions with a google search of the error message?

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the reply. Rebooting or closing down Excel & re-opening certainly helps. I have done a Google search, but no luck yet in resolving the problem. There are many people who seem to have the same issue as I have

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I think that is because excel has never been particularly good at memory management. Areas in the spreadsheet are not "cleared", VB code uses space that is not always cleared. [I try to set my objects to nothing at the end of code, but I am not sure that always works either.]

    If it were a recurring issue with particular workbook, I would suggest trying to clean up the workbook (even "recreating it") as it could be getting "corrupt". If it were particular code, I would try to clean up the code.

    But it seems to be intermittent and comes with different books and different macros...

    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    HowardC (2011-10-29)

  6. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the reply

    When I go the the office tommorrow, I will email you some of my code. Maybe You will be able to clean this up. I usully run a macro called Auto_Update, which is a combination of several macro combined into one. I will be going to a cricket match shortly and will be in touch tommorrow morning

    Regrds
    Howard

  7. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Excel cannot complete this task with Available Reources. Choose less data

    Hi Steve

    A have copied one of my macros below. After running the macro and then trying to open up another workbook, I get the message" Excel cannot complete this task with available resources. Choose less data or close other applications"

    It would be appreciated if this could be amended so as to prevent this from happening. I also found some software that can assist im inmproving performance, but am reluctant to use this unless it is recomended. It is called Rizone Memory Booster. Do you know anything about this program or can you recommend any software to assist with memorory leaks?

    Your assistance will be most appreciated



    Sub Auto_Update()

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Import_data
    Windows("easttb.xls").Activate
    Close_File
    Save_Data
    End Sub

    Sub Import_data()

    ChDir "C:\Extract"
    Workbooks.OpenText Filename:="C:\Extract\easttb", Origin:=437, StartRow:= _
    1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
    , Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1 _
    ), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
    Array(9, 1)), TrailingMinusNumbers:=True
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("A:C").Select
    Selection.Copy
    Windows("easttb.xls").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Range("A1").Select
    End Sub



    Sub Close_File()
    Windows("easttb").Activate
    ActiveWorkbook.Close
    End Sub

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    I don't know if this will solve your problem but it will at least make the code a little more efficient and easier to read. I tried to get rid of things as soon as you were done with them as far as I could tell from the code.
    Code:
    Public wkbMain as Workbook
    
    Auto_Update()
    
       Set wkbMain = ActiveWorkbook
    
       Application.DisplayAlerts = False
       Application.ScreenUpdating = False
       Import_data
       wkbMain.Activate
       Save_Data
    
    End Sub    'Auto_Update
    
    Sub Import_data()
    
       Dim wkbExtract as Workbook
    
       ChDir "C:\Extract"
       Workbooks.OpenText Filename:="C:\Extract\easttb", Origin:=437, StartRow:= _
          1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
          ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, _
          Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1), _
          Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
          Array(9, 1)), TrailingMinusNumbers:=True
    
        Set wkbExtract = ActiveWorkbook
    
        Columns("B:B").EntireColumn.AutoFit      ' If you're not saving the Text workbook
        Columns("C:E").Delete Shift:=xlToLeft    ' these lines can be replaced with
        Columns("D:E").Delete Shift:=xlToLeft    ' code in the 2 lines below
        Columns("A:C").Copy                      ' Range("A:B","F:F).Select
                                                 ' Selection.Copy
        wkbMain.Activate
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    
        wkbExtract.close '*** Done with this close now! ***
        Set wkbExtract = Nothing
    
        Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
           OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
           DataOption1:=xlSortNormal
        Range("A1").Select
    
    End Sub   'Import_data
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. The Following User Says Thank You to RetiredGeek For This Useful Post:

    HowardC (2011-11-05)

  10. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Retired Geek

    Thanks for cleaning up the code. Will test this in the course of this week & will let you know if it has helped. I am also getting in an IT specialist, as my IT Dept are not able to help.

    Regards

    Howard

  11. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve & Retired Geek.

    Just to give you feedback that my problem has now been sorted out, with regard to the following message coming up after running a macro and then opening a new workbook "Excel cannot complete this task with available resources. Choose less data or close other applications".

    I made use of an IT professional who sorted out the problem. He disabled some non-essential programs at Start Up such as Skype, Nero etc and he also loaded a Memory Booster utility that frees up Memory. You can set the memory booster to allways have a minimum amount of RAM available at alll times

    Where I had the following code in my macro, Application.screenupdating = False, Excel wouuld close down. It now no longer does this

    I am so glad that the problem has been resolved

    Regards

    Howard
    Last edited by HowardC; 2011-10-29 at 00:47.

  12. #10
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,421
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for all your help in the past

    I have posted a solution to the problem in this thread. I engaged the services of an IT Hardware professional, who sorted out the problem. The solution was to disable certain programs at start up for Eg Skype, Nero Etc which are not essential and to load a Memory Booster utility that allow one to set a mimimum amount of RAM that is available at all times

    Regards

    Howard

Posting Permissions

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