Excel 2010 crashing when closing file

Hi guys,

Created an application that was worwing perfectly fine with Excel 2007 but now I am encountering a few erros with 2010.

One of them goes as follows:

The macro opens a number of workbooks, copies data from certain ranges into the workbook where the macro resides and then closes the "data suppliers" workbooks.

With 2007 all workbooks were opened and closed with no errors. In 2010, the macro raises an error "object invoked has disconnected from its clients" when attempting to close a couple of workbooks. All workbooks were created using the same template and have a large Excel table in one of the sheets . The two workbooks that make Excel crash do not have any data on the table, just a unique and empty row and of course the table headers. If I happen to enter a new row on the table, then it will not crash. If I enter data on some of the fields of that unique row it may crash or not, depending on which fields are filled. Really weird.

So it is the table that it is causing these errors. If I remove it Excel closes the file fine.

I am not going to bother posting the code as I have removed absolutely everything for testing purposes reducing it to a simple open workbook and close workbook lines and Excel is still crashing.


Any suggestions?

Thanks very much.

Originally Posted by MarvinP

Hi Albatros,

I'd throw in a few DoEvents to make sure the OS has time to close the files and do things in other apps. See
http://support.microsoft.com/kb/118468

Thanks Marvin for your reply. I have narrowed down the problem and it is nothing to do with the code.

The workbook has a substancial amount of dynamic ranges that expand and contract as rows are inserted or deleted in the table. If I delete those named ranges 2010 will not crash when there is only one empty row in the table. As I said, closing the workbook programatically is what causes the crash.

The weird thing is that 2007 does not present this problem. So now I will play around with those named ranges and see if I can pinpoint which ones excel 2010 is not happy with.

This is the formula I have used to create the named ranges.
Named range
Oracle_ID

='Raw Data'!$A$7:INDEX('Raw Data'!$A:$A,Count_Raw_Data_Rows+6)

A is the column where the range sits. 7 is the first row on the table. Count_Raw_Data_Rows just counts the number of rows in the table. If I attempt to select the named range it does make the proper selection, whether the table has one or more rows.

There are many ranges of this kind that apply to several columns on the table.

Name Manager does not report any named ranges with errors.

Suggestions will be appreciated.

Originally Posted by MarvinP

Instead of using the whole column (like $A:$A), try using something that is defined (like $A7:$A10000) where 10000 is big enought to include any big data set you have.

Thankas Marvin for the suggestion. I believe that your idea does not have much effect in this case as Excel will only look at the used range when a whole column is specified and users are not allowed to enter any data below the table (it is password protected).

I believe I have found the culprit formula that Excel 2010 is not happy about.

=ROWS(DataTable)

DataTable is the name of the table. When the table has only one empty row, even though the formula correctly returns 1, for some reason Excel 2010 is not happy about it and it will crash when programmatically closing the workbook. If I change it to =COUNTA('Raw Data'!Z:Z)-1 it will yield the same result but this time it will not crash.

Weird, isn't it?