I am using Excel 97.

I have the following code :

For Each wb In Application.Workbooks
If wb.Name < ThisWorkbook.Name Then
wb.Close
End If
Next wb

At the end of the run of my macro, I have only two spreadsheets. The
Macro spreadsheet. And the report. Whenever it hits wb.close, and
spreadsheet is not the macro spreadsheet, Excel crashes.

I have tried several things. Including closing the spreadsheet by name
and not cycling the the spreadsheets. Nothing worked. C++ debugger
claims that it is a sharing violation. This is not true because I am
only one using the spreadsheet.

In order to get the job done. I save, then quit Excel. I do not want
this, I prefer the code above.

Does anyone know why wb.close might cause Excel to crash?

Rich,

my wild guess is that it's because you are trying to close the wb were the
macro is running
i.e., if your sub was called by the report from your macro book, then this
code will attempt to close the macro book, because Thisbook refers to the
active book - reports. but if macro book is closed, how can you continue to
run the code? So you probably screw stack or memory or something like that.

to solve this, add second test:

For Each wb In Application.Workbooks
If (wb.Name < ThisWorkbook.Name) and (wb< MyMacrobookName) Then
' where MyMacrobook is the name of your book with code, as a string
wb.Close
End If
Next wb

I am not sure about this, but give it a try
I would be curious to learn the results.

RADO



"Rich" wrote in message
om...
I am using Excel 97.

I have the following code :

For Each wb In Application.Workbooks
If wb.Name < ThisWorkbook.Name Then
wb.Close
End If
Next wb

At the end of the run of my macro, I have only two spreadsheets. The
Macro spreadsheet. And the report. Whenever it hits wb.close, and
spreadsheet is not the macro spreadsheet, Excel crashes.

I have tried several things. Including closing the spreadsheet by name
and not cycling the the spreadsheets. Nothing worked. C++ debugger
claims that it is a sharing violation. This is not true because I am
only one using the spreadsheet.

In order to get the job done. I save, then quit Excel. I do not want
this, I prefer the code above.

Does anyone know why wb.close might cause Excel to crash?