Symptoms of Excel Freezing with “Send Error Report” option

Topic

New Reply

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:

, 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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

First you send a file that doesn’t pertain and then one I can’t open

Reply | Quote

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/excel-crash-saving-file-gives-cvr-extension-t3103301.html

Reply | Quote

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

Reply | Quote

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

Reply | Quote

This should explain
http://superuser.com/questions/450014/clearmytracksbyprocess-all-options

Reply | Quote