CODEWITHSUNDEEP
vbaexcelsave
emmistar
emmistar

Reputation: 101

Excel cannot complete this task with available resources while trying to save

I am currently in the process of adding functionality to a VBA excel application that has been written previously. There have been no unusual issues until recently when I was editing a module and a mistake (which I have now fixed) caused the program to crash. Upon reopening I found that I was unable to save the document and received the error message “Excel cannot complete this task with available resources. Choose less data or close the other applications”. Closing other application or restarting the computer did not help.

Since then I have occasionally been able to get the program in an editable state by doing a random combination of the following:

• Changing the file name

• Opening the program, then cancelling the process then reopening

• Removing all the modules and reimporting them

Here are some links to MSDN information on trouble shooting this problem https://support.microsoft.com/en-gb/kb/2779852 and https://support.microsoft.com/en-gb/kb/2655178

When I do get the document in state where it saves the document. It will save fine until I close and reopen the excel document, then the saving issue frequently (but not consistently) occurs.

I need to get to the bottom of this for the users sake as this also affects functionality for them. Any help would be appreciated as it feels like I have tried everything.

More information about the program

The program is a database application, where the user can add data to the database and can also view the data in the form of a table as well as graphical representations. They do not have access to the excel sheets behind the program.

It currently consists of 19 sheets, 21 user forms (which are quite big), 20 modules and 8 class modules. It is just over 1600 KB in size.

When the excel file is opening the program it:

To open the excel and be able to view the code the user then selects the admin button and after typing the correct password the application is shown and all user forms are unloaded. It is at this stage that I try to save the program and call VBA to do my editing.

2/9/2015 Additional Information opening in safe mode also increases the probability of being able to save the workbook

Upvotes: 4

Views: 2469

Answers (3)

jsydrah
jsydrah

Reputation: 11

If your workbook performs web queries or makes data connections, chances are your new workbook will get huge again. I had the same problem and finally found a solution at https://youtu.be/ZsIDLnPf09U. The problem has to do with memory being used by stored web queries and data connections. This solution brought my workbook from GBs of memory usage down to MBs and showed me how to keep it that way.

Upvotes: 0

emmistar
emmistar

Reputation: 101

I was not conclusively able to find the source of my problem however I did find a method to stop it from occurring. I started a completely new excel sheet and imported all my code modules and all my excel spread sheets into it.

Upvotes: 0

Trum
Trum

Reputation: 630

It may not be your answer, but this error is often not caused by general file size (I have some sheets of around 40mb) but more general memory issues (latter versions of excel do seem to memory leak pretty badly)

One big thing I have found to look out for is blank rows to the side of and below active data in the sheets (you can easily find out by pressing control end and seeing where you end up)

Macros are sods for leaving the last row in place when data is deleted etc, and I've hit problems in the past caused by rogue rows (adding periodic save operations can be the key to avoiding this issue)

I hope this helps

Upvotes: 0

Related Questions