+ Reply to Thread
Results 1 to 6 of 6

Excel crashing when working with large files

  1. #1
    Registered User
    Join Date
    07-01-2010
    Location
    Portland, OR, USA
    MS-Off Ver
    Excel 2007
    Posts
    39

    Excel crashing when working with large files

    Hello,

    I've had problems with Excel crashing in the past, but now I've finally been able to narrow it down a bit more and identify a repeatable procedure that will crash Excel.

    My project is difficult to describe briefly, but basically it imports error log files from networked computers and does stuff with them -- the user can automatically create Pareto charts of error frequency or downtime, or search for a specific error, or graph hourly tool throughput compared to error trends, etc.

    For the throughput section of my program, the files are quite big; one day might be 25+ MB worth of text. I copy the whole file into a new excel sheet, run my analysis, copy the results to a "Data" sheet, and then close the new worksheet. I then open a different set of files (to get the error logs), analyze that, graph those results, and then I include the throughput data from my "Data" sheet with those error series.

    My program works fine for executing these operations, but I run into a problem when I try to save the sheets. I suspect that autosave triggers the same crash. Out of the 40+ tools that create these files, I'm currently interested in looking at 6 of them (4 are "WTS" tools, 2 are "ATS"). When I run the throughput analysis on multiple tools, and go to save my workbook, Excel crashes.

    If I run the program on 1 tool and save, Excel usually does not crash. I can run 1 at a time, and save after each run, up until maybe 3-4 tools have been run; at that time the save triggers a crash again.

    If I run the program on multiple tools but delete the sheets before saving, Excel does not crash. So I suspect that running my analysis corrupts the worksheets somehow.

    I have run CodeCleaner numerous times (if I run CodeCleaner after analyzing the files instead of saving, it crashes Excel as well). I have ensured that all modules have "Option Explicit" on the top (actually, i haven't checked if my forms have that; I'll do that now). I've restarted my computer, but this bug is consistent. I checked the Error Report contents; "Code: 0xc0000005", which my co-worker told me was a pretty generic code that won't help me much. I'm using Excel 2007, and it's not in compatibility mode.

    I noticed that running my analysis on the "ATS" tools is more likely to cause a crash. Those files are also typically larger than the WTS files.

    I've attached my .xlsm file... I realize you won't be able to import the files and get the same problem I had, but maybe it will help.

    Very grateful for any more ideas on what it could be, as people in my company will need to use my program, and it can't always be crashing like this.

    Thanks much,
    -Joe
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    07-01-2010
    Location
    Portland, OR, USA
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Excel crashing when working with large files

    Any thoughts? I've been googling this all day to no avail.
    I just rebuilt my workbook... copied and pasted the stuff from the "HideData" sheet, exported and imported my macros, re-did the conditional formatting... analyzed those 6 tools for throughput and, bam, same error.
    I'm really at a dead end with this one.

    Thanks,
    -Joe

  3. #3
    Registered User
    Join Date
    07-01-2010
    Location
    Portland, OR, USA
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Excel crashing when working with large files

    What about the office 2007 service packs? Or the hotfix thing ( http://support.microsoft.com/kb/968863 )? Should I do a fresh install of office?

    -Joe

  4. #4
    Registered User
    Join Date
    07-01-2010
    Location
    Portland, OR, USA
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Excel crashing when working with large files

    I just ran my program on another machine... exact same behavior. I don't think I'll try the Office re-install.

    it really just seems like my macros somehow corrupt the data worksheets that they produce. If I close the worksheets, the file saves fine. If i try to save the worksheets, it crashes.

    Is it possible that it's getting buggy because I'm adding a series into a chart on page N straight from data on page 2?

    Thanks,
    -Joe

    EDIT: I narrowed down the crash to the "DayReport" module. I commented out most of that text and ran the program, and it saved fine. I slowly uncommented more text and I found that this line causes problems:

    Please Login or Register  to view this content.
    I suppose I can find a workaround that doesn't involve deleting pivot tables... but why should this cause a problem?

    EDIT2: Take a look at this: http://www.mrexcel.com/forum/showthread.php?t=8472
    I think maybe I'm storing a lot of data in a pivotcache (I don't really understand or know about pivotcaches). I clear the pivottable, but the cache data remains. Someone on that thread quoted Microsoft: "If the PivotTable cache isn?t referenced by a PivotTable object, the PivotTable cache is automatically deleted before the workbook is saved." If deleting a too-large pivotcache is crashing excel, this would explain why saving triggers the crash.

    Now to find a way to clear the cache manually and see if that helps...
    Last edited by theradpotato; 08-04-2010 at 07:33 PM.

  5. #5
    Registered User
    Join Date
    07-01-2010
    Location
    Portland, OR, USA
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Excel crashing when working with large files

    OK, I think I have boiled down my question to one that someone will hopefully be able to address.......

    When I start Excel, it's using about 41,000KB in the task manager. I watch my program run and collect external data from 6 external files. At the end, the file size is about 250,000KB. I go through and delete all of the sheets that were added during the program and the memory is still at 250,000KB.

    It's my belief that excel crashes when I try to save the file and the memory usage is so high.

    Can anyone give me an idea what is causing this to accumulate like it is?

    I apologize for all the repeat posts, but I really need to get this figured out.

    Thank you,
    -Joe

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Excel crashing when working with large files

    What happens if you use:
    Please Login or Register  to view this content.
    Remember what the dormouse said
    Feed your head

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1