Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    Symptoms of Excel Freezing with "Send Error Report" option

    This "problem" is not new, and I don't blame Excel entirely.
    It's been the same since I started this project using Excel 2003, 2007, and now 2010 version.

    It only happens with the

    Do Until

    Code:
    <code or codes>, more Loops within the Loop
    Loop
    Code:
    Code
    The Workbook process is perpetual, never ending until the time order events list runs out which is on a specific Worksheet.

    The events may start from 10am and go through to 10pm.
    So I'm "asking" Excel/VBA to web query each event, crunch some numbers, log results to a text file.
    These form statistics.

    During the process, after about an hour, or at best 3 hours of perpetual web query and loops, I will get an error message on the screen, saying Excel has crashed, send an error report, and re-start excel.
    I can view the error log, but have no idea what it means.


    If I press the Escape key to stop the run, and then use the F8 key to check my coding, sometimes the duration from pressing the F8 to the next code is delayed, it seems "bloated". Unless I re-start the workbook, all seems to be OK till the next time I start the macro or use the F8 key.


    I have used many types of clearers within the Run of things, hoping this may clear up some memory or temp files such as;
    Code:
    Public Function ClearClipboard()
        OpenClipboard (0&)
        EmptyClipboard
        CloseClipboard
    End Function
    
    Sub CLEAR_CLIP_BOARD()
        Call ClearClipboard
    End Sub
    The only other clue I see is when I use CCleaner, it shows Internet Explorer Temporary Files.

    We already know a dynamic web query may use IE as part of it web query function, but none of my codes ask anything of IE browser. I do not require the browser to be open to make my web query.

    So I think, is it because Excel/VBA has a limit as to what it can do and at a certain point one may need to consider programming ?

    Or is there another clear method that has got to do with the computer's memory?

    I really don't know what else to try.

    Would posting the error report here be of help to see exactly where the problem originates from ?


    Thanks

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    You don't mention your excel version nor the type of web query. Can you provide a file or dummy file with code to look at. It probably can be streamlined.

  3. The Following User Says Thank You to Supershoe For This Useful Post:

    XPDiHard (2014-09-08)

  4. #3
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    dguillett@gmail.com
    You don't mention your excel version
    nor the type of web query.
    Office 2010 ( 3 pc license for students )

    Type of web query code.
    Initially it was a Record Macro and tweaked thereafter for the URL reference that comes from the Events list
    in, & Range("A2"),
    Code:
    With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;http://***.com/***/" & Range("A2"), Destination:=Range("$A$3"))
    
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlOverwriteCells
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
    '        .WebTables = "1,2,3,4,5,6,7,8,10,11,12,13,""pooldata"",55"
            .WebTables = "1,2,3,4,5,6,7,10,11,12,13,""pooldata"",55"
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
    End With

    I have uploaded a simple looped counter with a time start.
    Ran it this morning and after an hour, ( during breakfast) or so it ( the process of counting) became very slow.

    So it does not seem to matter if it's a large workbook or a simple counting Loop, Excel seems to slowdown and "bloat".
    If I stop the Loop with the Esc key, close and re-open the workbook it will be fine again at the beginning till it seems to "bloat".

    That is why I suspect it is something to do with memory or the way I'm coding.
    Attached Files Attached Files

  5. #4
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    here is the cvr error report file after the predictable crash.
    It's encrypted so there ain't a way that I know of to see exactly where the problem originates from or causes.


    Thanks

    had to be zipped or it would not let me upload as .cvr
    Attached Files Attached Files

  6. #5
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    First you send a file that doesn't pertain and then one I can't open

  7. #6
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    The first file is simply a example of a continuous Loop, it will eventually freeze.

    The second file is from the temp file folder that gets sent to Microsoft if one chooses to send the error report.
    This error report was created today after 2 hours of continuous web query, then it errored.
    The reason for the error may be contained in that encrypted file,
    If you unzip, it will open, but it's contents is encoded.


    The same question was asked here in 2007 when I searched Google, and no replies to same question I'm asking.
    http://www.pcreview.co.uk/forums/exc...-t3103301.html

  8. #7
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    I am commissioned to do a lot of external queries (mostly stock market) related but I don't see how I can be of help unless I see and test the actual file or at least an actual url

  9. The Following User Says Thank You to Supershoe For This Useful Post:

    XPDiHard (2014-09-15)

  10. #8
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    Solved

    Thanks to dguillett@gmail.com who suggested I try some codes.

    I believe it is SOLVED,
    but won't mark it yet from thread tools cos it closes the thread and further comments may be added..


    On Test,
    The web query ran for a few hours non stop and it got to the end of the events list as it's coded to do, instead of End Sub, it got to End, finally.

    The codes used were

    Code:
    Sub Clear_History()
    Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 1"
    Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 2"
    
    'Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 8 "
    
    'testing
    Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 8", vbHide
    AppActivate "Microsoft Excel"
    
    End Sub
    Last edited by XPDiHard; 2014-09-15 at 08:48.

  11. #9
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts

Posting Permissions

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