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?

 

[xenou]

To flush your memory chips:

Hit the power button or pull the plug.

Then restart.

ξ

 

[32CARDS]

To flush your memory chips:
Hit the power button or pull the plug.
Then restart.

Thanks xenou ,
Is there a VBA code to do this ?
ha haa


I want to try to run the VBScript file please,
using VBA to run that file, was my question.

Thanks in advance

 

[32CARDS]

Bump

Sorry to bump this thread.

I asked a question and invested posting my trial and errors of coding to make the VBScript code run using Excel macro.
Is there something wrong with my question?

For the sake of simplicity,

What is the macro code to run a a file ending in .VBScript

File is located in C:\Folder\ram.VBScript

Thanks

 

[xenou]

Perhaps you don't need to call a vbs script from a macro. Just put the vb code in the macro itself - it should run fine in a vba environment.

I have no idea what this is:

mystring=(8000000000000)

 

[xenou]

This worked from Excel vba to run a vbs script (it actually ran a bat file that ran a vbs script):

[COLOR="Navy"]Sub[/COLOR] foo()
[COLOR="SeaGreen"]'//Run a bat file that runs a vbs file[/COLOR]
[COLOR="SeaGreen"]'//Contents of bat file:[/COLOR]
[COLOR="SeaGreen"]'-----------------------------------[/COLOR]
[COLOR="SeaGreen"]'cscript C:\myTemp\Foo.vbs[/COLOR]
[COLOR="SeaGreen"]'-----------------------------------[/COLOR]
    
    Shell "C:\myTemp\foo.bat"


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

According to some sources this should work to run a vbs script directly but no joy for me - not even with a sleep command thrown in for good measure:

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Declare[/COLOR] [COLOR="Navy"]Function[/COLOR] Sleep [COLOR="Navy"]Lib[/COLOR] "kernel32" (ByVal dwMilliseconds [COLOR="Navy"]As[/COLOR] Long) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]

[COLOR="Navy"]Sub[/COLOR] bar()
[COLOR="SeaGreen"]'--------------------------------------------------------------------[/COLOR]
[COLOR="SeaGreen"]'//Run a vbs file[/COLOR]
[COLOR="SeaGreen"]'//http://ss64.com/vb/run.html[/COLOR]
[COLOR="SeaGreen"]'//http://msdn.microsoft.com/en-us/library/aew9yb99%28v=vs.85%29.aspx[/COLOR]

[COLOR="SeaGreen"]'//Should work but fails[/COLOR]
[COLOR="SeaGreen"]'--------------------------------------------------------------------[/COLOR]
[COLOR="Navy"]Dim[/COLOR] wSh [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]
    [COLOR="Navy"]Set[/COLOR] wSh = CreateObject("WScript.Shell")
    wSh.Run "cscript C:\myTemp\Foo.vbs", , True [COLOR="SeaGreen"]'//Should run script and wait for it to finish[/COLOR]
    Sleep 5000 [COLOR="SeaGreen"]'//try an additional "wait" period for script to run[/COLOR]
    [COLOR="Navy"]Set[/COLOR] wSh = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="SeaGreen"]'--------------------------------------------------------------------[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

 

[John_w]

What is the macro code to run a file ending in .VBScript

File is located in C:\Folder\ram.VBScript

Does this file work ok (i.e. open and run) if you double-click it in Windows Explorer? I'd be surprised if it does as the usual extension for a VBScript file is .vbs, not .vbscript, though it depends on your file associations. Try renaming it to ram.vbs and then this VBA code should run it:

Sub Shell_It()
    Dim cmd As String
    cmd = Chr(34) & "C:\Folder\ram.vbs" & Chr(34)
    Shell Environ("COMSPEC") & " /k " & cmd, vbNormalFocus
End Sub

The /k keeps the command window open, allowing you to see the executed commands and any errors. Changing it to /c will close the window after all the commands have executed.

NB - Xenou's Sub bar() worked for me.

The project does real time updates from financially 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.

How is it doing real-time updates? By Winsock, DDE, IE automation, web query, XML, or WinHttp, etc. or something else?
I can't see how renewing your IP address or flushing the DNS cache will help with a web page problem - won't that lose the connection/session? Maybe you need to clear the IE cache.

 

[xenou]

Thanks for the feedback - I'm glad it worked for you. Perhaps it's something about what my foo.vbs script actually does. I was using it to write a line to a text file. I did see the command window flash by so it was "doing something" - but nothing in the text file.

This was not the case when I ran foo.vbs from the bat file or directly by double clicking it (both okay).

Foo.vbs

On Error Resume Next
fPath = "C:\myTemp\Scheduled\log.txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set ts = objFSO.OpenTextFile(fPath, 8, True, -2)
ts.WriteLine CStr(Now()) & " testing foo ... "
ts.Close
Set ts = Nothing
Set objFSO = Nothing

 

[32CARDS]

John_w

The web query code runs in a DoUntil Loop and it will run indefinately due to the call
before the End Sub.
It never does Run indefinately though because there are paramters to do the next thing when certain information is gathered from the web page.

I posted the web query code so you can see how it gets to web page.

All information is extracted, pasted to a worksheet and from the work sheet I work my numbers.
Ultimately I would like it to print to a dot matrix printer, drink coffee and this "slave" does all the work.
The theory is, I view everything from the print-outs.

It's been done before uisng a Firebird Server, Relational database and script editor.

At the time the real-time data feed was not internet based. ( pre 2005 )
When the real+time data feed service ended it's service and switched over to full internet, the programmer who made this application for me was no longer available for upgrades.

So, I started to make my own using VBA.
So far where it's at is,
A "Psudo Server", ( no database ) and the same "logic" of my script that used to run with Firebird.

To answer your question specificaly
The VBA I use to get that very same real-time data is:

Sub GETRESULTS()
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

On Error GoTo notfound
Sheets("UNIT_1").Select
Sheets("UNIT_1").Range("A3").Select
With Selection.QueryTable
.Connection = _
"URL;http://xyzwebpage.com.au/" & _
Sheets("UNIT_1").Range("A1") & ".html"
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

Application.Wait (Now + TimeValue("0:00:02"))' test to see if this may stop freeze
Loop
TRANSFER_UNIT_1 'CARBON COPY
LOGRESULTS
notfound:
'RETRY_1
'''MsgBox "UNIT_1-RESULTS-ERROR-KLIK TO RE-TRY"

If Sheets("ZEROWUN").Range("E12").Value = "notfound-results" Then MACRO_NotFound
Sheets("ZEROWUN").Range("E2").Select
If Sheets("ZEROWUN").Range("E2").Value = "1" Then Macro_5

'''Sheets("ZEROWUN").Range("H24").Select
GETRESULTS

End Sub

 

[32CARDS]

xenou

I'll know in a few days after testing....these tests takes a dedicated set of time and must start at a certain time of the day, if too late, it's too late, if too soon, not enough updated information required from specific web page.

Thanks