How do I control a program called via SHELL

[calandrelli]

From Excel (VBA) I am opening a program without any VB objects (Document Direct) by executing a .bat file that contains a script. Document Dirtect downloads a report from a MVS system into a giant text file (40 - 50 Megs). Since I have no control of the Document Direct program, the only way that my code knwows that it is done is by looking at the file size of the document it is downloading every minute or two and compairing its file size to its previous size. Once the two match I know it is done and can allow the code to move on. The problem arrises when network traffic is heavy and the Document Direct stalls out for more than a minute and the code moves on prematurely. Is there a better way to control the external program? Can I detect when the program is open and closed through windows?



'***********************************************************
'*Open Document Direct script *
'***********************************************************
'
Shell PATHNAME:="\\addapdp1\pbo_db\Databases\LateJobs database\Bat Files\AMM31 SCRIPT MON.bat", _
WindowStyle:=vbNormalFocus
'Set a file system to examine files outside of Excel
Set fs = CreateObject("Scripting.FileSystemObject")
T = Time
Application.Wait (T + 0.0007)
Set f = fs.getfile("\\addapdp1\pbo_db\Databases\LateJobs database\Document Direct Reports\Latejobs_CURRENT.RPT")
Do
Test1 = f.Size
T = Time
Application.Wait (T + 0.0007) 'delay 60 seconds
Test2 = f.Size
Loop Until Test1 = Test2

 

[ETID]

Maybe this FAQ will help you out....


faq707-2542

 

[PHV]

You can try something like this:

Set sh = CreateObject("WScript.Shell")
cmd = "\\addapdp1\pbo_db\Databases\LateJobs database\Bat Files\AMM31 SCRIPT MON.bat"
rc = sh.Run("%COMSPEC% /C """ & cmd & """, 1, True)

Your VBA code will wait for the completion of the command and get the return code in rc.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[calandrelli]

PHV, The last line is causeing an syntax error and I don't understand what it is doing. Could you please explain.

rc = sh.Run("%COMSPEC% /C """ & cmd & """, 1, True)

 

[PHV]

As the path to your bat file contains spaces you have to enclose them within quotes.
Sorry for the typo, try this:

rc = sh.Run("%COMSPEC% /C """ & cmd & """", 1, True)

or this:

rc = sh.Run("%COMSPEC% /C " & Chr(34) & cmd & Chr(34), 1, True)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[calandrelli]

Thank you for the help, but it is not going to work for me. It was holding the code for the .bat but not the program that the bat file was running. This was not a huge problem...I just dropped the command line directly into the code instead of using the bat file. This is when I found out that the Document Direct program "RDSWIN.EXE" that I wall calling was then opening another application "MAINRDW.EXE" to complete the rest of the download and the RDSWIN.EXE was closing directly after the splash screen closed. In the end the code is still not holding.

 

[ETID]

Have you tried the FAQ listed above?