Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel crashes on workbook.close (Excel 2000)

    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??

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel crashes on workbook.close (Excel 2000)

    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:="<=" & EndTaxOfficeNum & ""
    Set rngVisible = Sheets("Data").AutoFilter.Range

    can probably be

    Set rngVisible = Sheets("Data").Range("A1").AutoFilter Field:=5, Criteria1:=">=" & StartTaxOfficeNum & "", _
    Operator:=xlAnd, Criteria2:="<=" & EndTaxOfficeNum & ""

    and there are more.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel crashes on workbook.close (Excel 2000)

    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

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel crashes on workbook.close (Excel 2000)

    Your confidence may be misplaced. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> 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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel crashes on workbook.close (Excel 2000)

    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

  6. #6
    New Lounger
    Join Date
    Apr 2004
    Location
    San Francisco, California, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel crashes on workbook.close (Excel 2000)

    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.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel crashes on workbook.close (Excel 2000)

    Please post the code; if it's extensive, post it as an attachment.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •