Excel VBA Macro Running DOS Application !!

Hello;

I would very much appreciate your expertise on how-to!

In myBook1.xls, I've a VBA macro, say, Sub myMacro (), which generates,
compiles, and saves a specially unformatted data file, say, myInputFile =
"myInp123.inp", and assigns a name to be used later for an output file, say,
myOutputFile = "myOut123.out" (for tracking purposes only).

All files are in ThisWorkBook.Path. In the same folder, I've also the DOS
program myProgram.exe.

Here're the steps I currently follow successfully for a single run:
1. open myBook1.xls, run myMacro, and take note of the *.inp and *.out files
names
2. quit Excel
3. open DOS window by clicking the Command Prompt shortcut
4. change DOS directory at the prompt to that of ThisWorkBook.Path, either
by typing the Cd command several times, or by typing the name of the Batch
file, say, myBatch.bat, which resides in the folder that the Command Prompt
opens at (fixed location, say, C:\My Files>).
5. type at the new DOS prompt the program name and the 2 re-directed files:
..........> myProgram < myInp123.inp > myOut123.out (return)
6. exit the DOS window, simply by typing Exit
.....(The above steps 1 to 6 work fine with no problem)

7. intend to repeat steps 1 to 6 above, say, 100 times or so until an
acceptable covergence is achieved (determind in Sub myMacro ()).
.....(you may disregard steps 1, 2, 6, 7 above)

Q: Can the above steps 3, 4 and 5 be coded in Sub myMacro () ?? ... or even
better, in a separate macro in the same standard module, probably using the
Public variable names myInput and myOutput ???

Your answer maybe: "It's very easy! Try this ...", or: "It's not possible
.... forget it", or, preferably, something in-between !!

Thank you kindly.

raypayette;

Thank you for your reply. It has been a while since I used the Shell
Function, and it was good to remember it!!

1. There's no need for the opened Command Prompt window to be visible for
the following reasons.

2. From the Excel VBA macro:
.....a. I need to change the Command Prompt directory to the macro designated
folder, say: mySeriesFolder
.....b. run the DOS program myProgram.exe with the macro designated input and
output files, say: myProgram < myInputFile > myOutputFile
where:
........ mySeriesFolder = "C: \ My Files\ MySeries???"
........ myInputFile = "myInp???.inp"
........ myOutputFile = "myOut???.out"
........ ??? is a 3-digit unique designation assigned within the macro for
each run using random number generators.

3. The idea of using a batch file may not be a practical one, since for each
run, it has to be created using the unique ??? designated by the macro, save
the batch file, and then use it in the macro!!

4. Instead, it would be much simpler if I know, once the DOS window is
opened (and made invisible), to include (after the Shell statement) the VBA
code for:
......> Cd mySeriesFolder
......> myProgram < myInputFile > myOutputFile
with the proper declaration for the variables mySeriesFolder, myInputFile,
myOutputFile as strings (ref 2.b above).

Your help would be greatly appreciated.

"raypayette" wrote:

>
> To access DOS commands use either:
> Sub test()
> Shell "C:\WINNT\system32\cmd.exe", vbNormalFocus
> 'RetVal = Shell("C:\WINNT\system32\cmd.exe", 1)
> End Sub
>
> Once you are in DOS, use a batch file to do your work!
>
>
> --
> raypayette
>
>
> ------------------------------------------------------------------------
> raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
> View this thread: http://www.excelforum.com/showthread...hreadid=552733
>
>

Hello;

Here's a simple macro (XL 2003, Win XP) that produces absolutely nothing! No
errors and no output file !!

Sub Test2()
' To run a DOS application with re-directed input and output files, from
this Excel VBA macro
' The DOS program LL107.exe and the 2 re-directed files are in
ThisWorkBook.Path
' Here, I'm trying to immulate the DOS command line:
' C:\My Files\General\MacroToRunDOS>LL107 <myInpFile >myOutFile (return)
' specify the names of the input & output files, and the full path
myInpFile = "LL107_" & Range("F19") & Range("G19") & ".inp"
myOutFile = "LL107_" & Range("F19") & Range("G19") & ".out"
myPath = ThisWorkbook.Path
RetVal = Shell(myPath & "\LL107.exe" & " <" & myInpFile & " >" & myOutFile, 1)
End Sub

By stepping into the macro, the Command Prompt window appears momentarily,
and then disappears!
A successful run of the above macro (or similar) should produce the results
in myOutFile in this folder.

Any suggestion(s) ?? Thank you.


"monir" wrote:

> raypayette;
>
> Thank you for your reply. It has been a while since I used the Shell
> Function, and it was good to remember it!!
>
> 1. There's no need for the opened Command Prompt window to be visible for
> the following reasons.
>
> 2. From the Excel VBA macro:
> ....a. I need to change the Command Prompt directory to the macro designated
> folder, say: mySeriesFolder
> ....b. run the DOS program myProgram.exe with the macro designated input and
> output files, say: myProgram < myInputFile > myOutputFile
> where:
> ....... mySeriesFolder = "C: \ My Files\ MySeries???"
> ....... myInputFile = "myInp???.inp"
> ....... myOutputFile = "myOut???.out"
> ....... ??? is a 3-digit unique designation assigned within the macro for
> each run using random number generators.
>
> 3. The idea of using a batch file may not be a practical one, since for each
> run, it has to be created using the unique ??? designated by the macro, save
> the batch file, and then use it in the macro!!
>
> 4. Instead, it would be much simpler if I know, once the DOS window is
> opened (and made invisible), to include (after the Shell statement) the VBA
> code for:
> .....> Cd mySeriesFolder
> .....> myProgram < myInputFile > myOutputFile
> with the proper declaration for the variables mySeriesFolder, myInputFile,
> myOutputFile as strings (ref 2.b above).
>
> Your help would be greatly appreciated.
>
> "raypayette" wrote:
>
> >
> > To access DOS commands use either:
> > Sub test()
> > Shell "C:\WINNT\system32\cmd.exe", vbNormalFocus
> > 'RetVal = Shell("C:\WINNT\system32\cmd.exe", 1)
> > End Sub
> >
> > Once you are in DOS, use a batch file to do your work!
> >
> >
> > --
> > raypayette
> >
> >
> > ------------------------------------------------------------------------
> > raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
> > View this thread: http://www.excelforum.com/showthread...hreadid=552733
> >
> >

raypayette;

Thank you for the ref. web site.
I'll post my simple macro at MrExcel Forum to see if someone could advise on
how to use SHELL with re-directed input and output files.

Thanks again.

"raypayette" wrote:

>
> Here's a web site that might help!
> http://www.mvps.org/dmcritchie/excel/shell.htm
>
>
> --
> raypayette
>
>
> ------------------------------------------------------------------------
> raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
> View this thread: http://www.excelforum.com/showthread...hreadid=552733
>
>