Excel crashes on workbook.close (Excel 2000)

Topic

New Reply

My macro copies a varying number of rows of data to a sheet within the same workbook, moves the sheet to a new workbook, saves and closes the new workbook. It has to do this about 80 times. It crashes Excel after the 3rd time when it’s run, and after the 6th time when I step through it.
I checked the drwatson error log which tells me, among many other incomprehensible things, “Application exception occurred: … Exception number c0000005 (access violation)” The only references I can find to this refer either to saving as a Web page and file corruption – not helpful.
I’m running Excel 2000 Sp3 under Windows 2000.
The error seems to occur when I close the new workbook after saving it.
What have I done/not done??

Reply | Quote

Replies

I don’t have any bright ideas (I don’t easily understand this type of code without seeing the data its being run against), but on a cursory glance, see if releasing the Workbook object helps:

wbkLetter.Close
Set wbkLetter = Nothing
End If

Also the references to ActiveSheet are hard to follow; you might want Set your source and target worksheets as Worksheet objects and act on the Objects instead of switching ActiveWorksheets back and forth.

Another possibility is that after every .Copy operation, set CutCopyMode off by

Application.CutCopyMode = False

You also have a lot of “Selection” code that could be avoided, for example:

Range(“A1″).CurrentRegion.Select
ActiveWorkbook.Names.Add Name:=”TaxOfficeAddresses”, RefersToR1C1:=Selection
ActiveCell.Select

can probably be

ActiveWorkbook.Names.Add Name:=”TaxOfficeAddresses”, RefersToR1C1:=Range(“A1”).CurrentRegion

and

Range(“A1″).AutoFilter
Selection.AutoFilter Field:=5, Criteria1:=”>=” & StartTaxOfficeNum & “”, _
Operator:=xlAnd, Criteria2:=”=” & StartTaxOfficeNum & “”, _
Operator:=xlAnd, Criteria2:=”<=" & EndTaxOfficeNum & ""

and there are more.

Reply | Quote

John,
WOW! Great, practical suggestions. Thanks for taking the time – I need someone to show me how to improve my code. After a quick glance at your suggestions I am confident the macro will run faster and higher than ever before.
Ross

Reply | Quote

Your confidence may be misplaced. If you are getting into Excel VBA macros and coding, a Search of this forum and the Excel forum will give you several recommendations on books which do a good job of teaching you more about Excel VBA. And if the problems in your code are not resolved, please post back to this thread.

Reply | Quote

John,
My primary reference book is Walkenbach’s Power Programming, but no book covers it all, and the code works like a charm now.
Thanks again.
Ross

Reply | Quote

John,
My primary reference book is Walkenbach’s Power Programming, but no book covers it all, and the code works like a charm now.
Thanks again.
Ross

Reply | Quote

Your confidence may be misplaced. If you are getting into Excel VBA macros and coding, a Search of this forum and the Excel forum will give you several recommendations on books which do a good job of teaching you more about Excel VBA. And if the problems in your code are not resolved, please post back to this thread.

Reply | Quote

John,
WOW! Great, practical suggestions. Thanks for taking the time – I need someone to show me how to improve my code. After a quick glance at your suggestions I am confident the macro will run faster and higher than ever before.
Ross

Reply | Quote

I don’t have any bright ideas (I don’t easily understand this type of code without seeing the data its being run against), but on a cursory glance, see if releasing the Workbook object helps:

wbkLetter.Close
Set wbkLetter = Nothing
End If

Also the references to ActiveSheet are hard to follow; you might want Set your source and target worksheets as Worksheet objects and act on the Objects instead of switching ActiveWorksheets back and forth.

Another possibility is that after every .Copy operation, set CutCopyMode off by

Application.CutCopyMode = False

You also have a lot of “Selection” code that could be avoided, for example:

Range(“A1″).CurrentRegion.Select
ActiveWorkbook.Names.Add Name:=”TaxOfficeAddresses”, RefersToR1C1:=Selection
ActiveCell.Select

can probably be

ActiveWorkbook.Names.Add Name:=”TaxOfficeAddresses”, RefersToR1C1:=Range(“A1”).CurrentRegion

and

Range(“A1″).AutoFilter
Selection.AutoFilter Field:=5, Criteria1:=”>=” & StartTaxOfficeNum & “”, _
Operator:=xlAnd, Criteria2:=”=” & StartTaxOfficeNum & “”, _
Operator:=xlAnd, Criteria2:=”<=" & EndTaxOfficeNum & ""

and there are more.

Reply | Quote

I’m having the same problem – Excel 2000 crashing when I close a file, generating an access violation, but not when I step thru the code. I’ve tried exporting and importing the module, I’ve set every object to Nothing when done, I’ve tried saving the file before closing, all to no avail. Does anyone have any other suggestions? Thanks.

Reply | Quote

Please post the code; if it’s extensive, post it as an attachment.

Reply | Quote

Please post the code; if it’s extensive, post it as an attachment.

Reply | Quote

I’m having the same problem – Excel 2000 crashing when I close a file, generating an access violation, but not when I step thru the code. I’ve tried exporting and importing the module, I’ve set every object to Nothing when done, I’ve tried saving the file before closing, all to no avail. Does anyone have any other suggestions? Thanks.

Reply | Quote