Running VBScript files to flush memory

[32CARDS]

Hi all,
I'm back after a long spell from computer etc.
last time I fired up my VBA project was November 2007 according to the date files.

So, on with it.

Last time I worked on my project using dynamic web querie in VBA, excel freezes the entire PC, the only way is to Ctl Alt Del to get task manager up and then click on Excel to shut it down

In my code I have DoEvents before and after at the point of freeze when the project is running, to no avail.
I have tried many other suggestions, and the same project has been on several entirley different computers, still it will freeze after a period of time.

The project does real time updates from financialy related web pages, and it is during the refresh phase where it seems to freeze.
When I shut doen excel from task manager and re-open, start the maco, it runs as if nothing has occured.

I assume it has to be something to do with memory clogging up

My question is this

One of the flushing methods I use is to code

ipconfig /renew
ipconfig /flushDNS
ipconfig /registerdns
and the file is saved as a .BAT

file saved as
C:\folderA/REPAIR.BAT

VBA
Sub repair_ip()

Shell ("c:\folderA\REPAIR.bat")

End Sub

This suggestion did not stop the freeze.
I run this code at various points of the entire process.
---------------------,


The next code suggested has to do with flushing the memory chips.
I got this from googling ages ago, I am not sure exactly where.

But the Code is in VBScript

inside the file is:

mystring=(8000000000000)
It is suppose to free up memory

name of file is; "ram.VBScript"
,made by using notepad.

Therefore

C:\folderA/ram.VBScript

have tried VBA code to run this file and test the project to see if it will freeze

Sub ram()

Shell ("c:\folderA\ram.VBScript")

End Sub

And have also tried as a BAT file

Sub ram()

Shell ("c:\folderA\ram.BAT")

End Sub

Both trials did not work

-----------
But this does work

VBA
Sub repair_ip()

Shell ("c:\folderA\REPAIR.bat")

End Sub
--------------,

How can I get

mystring=(8000000000000)

to work in VBA?

 

[32CARDS]

Ok,
I have been testing the project all day today, and found the so called "flush memory" VBS thng did not make much difference, if any at all.
But thanks for the help with the code, it ran OK never the less.

In fact the entire Workbook with VBA opened, when I was probing the Run using the F8 key, got to the point just after a web query when the data came in,
at this point>
<<<.Refresh BackgroundQuery:=False
End With >>>>

when I mouse clicked the VBS script, it all froze, it looked like Excel was "melting" and was not able to recover.
Therefore it is not of any use to include in the scripting.
At least it was tested.

On another manual F8 test, one of the Error Handlers was activated at the same time I had a minor line drop out with the internet connection.
This does happen from time to time, but not the primary cause.

Average time it takes to Run and then freeze is around 10, 15 or 20 minutes on a "good" day.

Other than that the project's logical process seems to be working fine, just these minor glitches.

The next question is,
Is there something that it may possibly have to do with a build up of " VBA process junk", or some Windows XP OS process that may be running in the background and is "clashing" ?

( I have included DoEvents at various points of the VBA process, I was told this allows other events to run before VBA keeps Running, just incase, but makes no difference.)

CCleaner has found nothing in IE cache, because the process does not use IE.


The point of freeze is usually at the web query end of the code, something "jams it up".
It may take while sometimes to connect to the target URL, does not freeze at that point.

It's after the data is in, then it freezes.

I hope the clues may help the experts, even the programmer who showed me the dynamic web query code is baffled.

Any suggestions would be appreciated so I can test again and let you know back here.

Thanks

 

[John_w]

"CCleaner has found nothing in IE cache, because the process does not use IE."

Possibly because one of the web page headers is 'no-cache', but web queries use IE components to retrieve the data so you could try the following code which clears the IE cache (temporary files) every 50 web queries:

    Dim numQueries As Long
    
    numQueries = 0
    Do Until _
        Sheets("UNIT_1").Range("D4").Value = _
        Sheets("UNIT_1").Range("C1").Value Or _
        Sheets("UNIT_1").Range("D4").Value = _
        Sheets("UNIT_1").Range("D1").Value
        
        'Insert your refresh query code here
        
        'Clear IE temporary files (cache) every 50 web queries
        numQueries = numQueries + 1
        If numQueries Mod 50 = 0 Then Shell "RunDll32.exe InetCpl.Cpl, ClearMyTracksByProcess 8"
    Loop

Change the 8 to 10 to delete IE cookies as well.

 

[32CARDS]

Thanks John,
it will be a few days till I get back with the test results, it takes a dedicated whole day, but will run the tests and some sort of process logging.

 

[32CARDS]

Update,

I ran tests over 2 days, and I must admit the "flushdns" improves overall PC performance.

The project did not freeze to the point where I had to press
Cntrl-Alt-Del to get to Task Manager so to close Excel.
The flushdns solution allowed me to tweak the process in VBA and clean up a few errors of my own.
All good up to here.

BUT,
due to the way my "web query list" is obtained in the mornings, it may have upto 200-300 web queries in total.
Not all URL references are required through a filtering process.

In some cases, about 10% of the filtered list, things may have changed during the course of the day, therefore the 9am list would in effect be "outdated" by 1.00pm for example.
A new list is not available once the process has commenced at 9.00am

Out of that filtered list, 10% of queries will have the "Run-time error "1004":

The error message reads:
-----------------------------
"Run-time error "1004":
Unable to open http//webaddress/html
Internet site reports that the item you requested could not be found.
(HTTP/1.0 404)

End Debug
--------------------------
The Run stops at this point and VBA code is hi-lighted in yellow at this point:
.Refresh BackgroundQuery:=False


I press end and then press my macro button to start over and do the next querie on the list.
All works fine until it finds another "Run-time error "1004": !!

How can I over-ride this so my VBA goes to that same button I press without it stopping?
So for example,

If "Run-time error "1004": then

Sub Start-process ()

?
Or. ideally,

If "Run-time error "1004": then
Cell range = 1
End if
If Cell range A1 = 1 Then
Macro2
End if

Note:
This does not mean yet that the freeze problem has been solved yet, but so far due to the flush/dns, another glitch has been uncovered. I am not yet sure if I will find any other glitches when the "Run-time error "1004": is sorted.

BTW
the Dynamic Web querie code is not the one I posted earlier, it's another one for this section of the process:
The structre of the code is :

Note:"flushtest" is your code, have placed independantly so other web queries sections can activate it to.
------------------------
Dim numQueries As Long

Sub Macro1()

Sheets("Sheet5").Select
Sheets("Sheet5").Range("A1").Value = _
Sheets("Sheet6).Range("B7") 'NEW REFERENCE

With Selection.QueryTable
.Connection = "URL;http://mywebpage.com.au/" _
& Sheets("Sheet5").Range("A1") & ".html"
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
flushtest
.Refresh BackgroundQuery:=False ' stops here when 1004 error appears.
End With
End Sub

 

[32CARDS]

UPDATE #2 !

After some tedious searching, it seems many have this 1004 error

In Help files, solutions appear, but each case may not be the same depending where the web query is coming from.

The Help files show one possible solution;
----------------------------------------------
' Occurs after a query is completed or canceled.
'
Private Sub QueryTable_AfterRefresh(Success As Boolean)
'Success True if the query was completed successfully.
'
'example
'This example uses the Success argument to determine which section of code to run.
'
Private Sub QueryTable_AfterRefresh(Success As Boolean)
If Success
' ' Query completed successfully
Else
' ' Query failed or was cancelled
End If
End Sub
------------------------

Therfore, in my scenario, the "ideal" option is, ( because I already know it's a web page error)

would probably be after
Else
'add in
Macro2 ' my macro to go to the very next query
End if
End Sub
???

I now have to figure out how to implement the code correctly, some help would be appreciated.

Thanks

 

[32CARDS]

Update # 3

Excel "fell over" when most VBA process glitches seemed solved.
Had the bigger error message with the "Send Report" option, ( was not the VBA error during process) and then Excel is shut down altogether, something to do with corrupt files.
This has happened before after continual shutdown of Excel when it froze the whole computer, and having to use Task Manager to re-gain control.
I'll try again by un-installing and re-installing Office and give it another go, benefit of doubt due to the probable solutions suggested on this thread and not given a real chance to be fully tested.
It may take a few weeks,
Other than that, when it ran, it went like clockwork...so close, yet so far to having that cup of coffee, unobstructed !

 

[32CARDS]

Solved!

I ran the same workbook, perpetual ( Loop ) dynamic web queries now for 3 days, upto 10 hours per day in some cases.
All I did was upgrade to Office 2007 and used the same Workbooks.
The "freeze" did not occur as it constantly did in the old Office Suite.

I need now to get used to all these extra buttons with the new MS Office.

Minor issues, more to do with XP background "noises" running, but process of elimination I think will sort this out in time.

I can now get on with my project, and hope the data I crunch can get out to real world.
Still a long way to go in development, but at least this "freeze" issue does not seem to happen anymore, not any sign of it. The process ran better than expected.

We may never really know why it kept freezing the way it did when using the older version of MS Office.


Thanks to all who tried to help.