Excel 2007 SP1 - Crash (needing reboot) on "ActiveWindow.Close"

[Ron West]

I have a set of spreadsheets that work fine (auto-launched at 5am unattended)
in Excel 2003.

There is a central control workbook that calls each of a list of up to 20
data manipulation spreadsheets in turn, according to which of the checkboxes
in a column in the main spreadsheet are checked. Each worker spreadsheet is
opened, executed, and then closed, originally using "ActiveWindow.Close".

Unfortunately, when migrating to Excel 2007 SP1, the "ActiveWindow.Close"
line causes Excel to crash so fundamentally that other applications (such as
BrioQuery) stop working properly - so I have to reboot the machine.

I have tried using alternatives to "ActiveWindow.Close" - for example
Worksheets("name").Close, objectname.Close, etc, but it makes no difference.

Eliminating Compatibility Mode (saving all activated worksheets as .XLSM)
made no difference.

I’ve also done some research via Google and came across some pages that gave
solutions to similar problems - for example using ActiveX buttons from the
Toolbox to call the code instead of "Form" buttons - but none of them made
any difference.

Any ideas?

 

[Ron West]

I've narrowed down the cause of the crash.

It seems that if the "ChangeLink" method is executed, then closing the
workbook crashes Excel. If that method is commented out, then it doesn't
crash. Over to you , Microsoft...

____________________________________________________


'Module Level definition
Public wbModel As Excel.Workbook

Function savereports()
Dim strAWName As String
Dim wbReport As Excel.Workbook

( ... DATA LOADED INTO SPREADSHEET ... )

ActiveWorkbook.SaveAs Filename:=fpReport, FileFormat:=xlNormal,
Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

strAWName = ActiveWorkbook.Name
Set wbReport = ActiveWorkbook


'-----------------------------------------------------------------------------------------
'IF THIS LINE IS COMMENTED OUT THEN THE EXCEL CRASH CEASES
wbReport.ChangeLink Name:=wbModel.Name, NewName:=wbReport.Name,
Type:=xlExcelLinks
'-----------------------------------------------------------------------------------------


Workbooks(strAWName).Close

End Function

 

[Barb Reinhardt]

FWIW, you're not going to get an answer from Microsoft on this site. Maybe
someone can help though.

 

[Ron West]

Hopefully someone at Microsoft will read it! This is a forum hosted on
Microsoft.com!

I would have thought this particular set of threads dealing with "Excel
Application Errors" would be of special interest to Microsoft, compared to
the normal "How do I do this...?" posts. Is there someone on the Excel team
that someone could email, to suggest that they monitor new posts here?

My progress so far with my problem is to just try deleting the link as there
do not seem to be any obvious holes in the resulting data. OTOH, doing this
gives me a "Compatibility Checker" dialog stating that there are 5 links in a
location "Defined Names" that may have a "Minor Loss of Fidelity" - which is
puzzling as there is nothing I can find in the sheets/ranges/etc called
"Defined Names", plus the "Edit Links" menu-ribbon button is grayed out...

 

[Arie van Klompenburg]

Hi RonWes,

I have the same problem as you described on eggheadcafe.com. When doing a ChangeLink in a sheet, then Excel 2007 crashes when closing the Excel-application. On Excel 2003 this works fine.

Do you have this problem solved?



EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng