running command line programs from Excel

[Guest]

I have a program that I can presently only run using the command line window
(which used to be the MS-DOS window I guess). I would like to integrate this
program with programs I've written in VBA. Is it possible to call "DOS"
programs using VBA code?

Help would be greatly appreciated.

Cheers,

Thomas

 

[Norman Jones]

Hi Thomas,


See the Shell funciton and example in CVBA help

 

[Guest]

One way is to put your DOS commands in a batch files and call this from Excel

Sub rundos()
Call Shell("c:\test.bat", 1)
End Sub

Runs test.bat in the root of C

Mike

 

[Keith74]

I think the code you want is

Shell Environ("comspec")

but read the help file first

hth

Keith

 

[teepee]

Sub runbat()

Call Shell(Environ$("COMSPEC") & " /c c:\mybat.bat", vbNormalFocus)

End Sub

 

[Guest]

Thanks to all that replied, great help! But I have a couple of follow-up
questions: The program that I want to excecute needs the names of its input-
and output-files and more, so that when I run this program from the command
line, I would type something like:

nameofprogram inpputfile programmodule >outputfile

....in one line and then hit enter. The program produces output in form of
text files. I tried with call Shell ("(the line above)",...), but that did
not seem to work.

I would also need the VB program to pause until the DOS program has
finished, so that it can grap the outputfile and process it.

Can this be done???

Cheers,

Thomas

 

[stephen]

I am trying to use redirection and it does not work.
***********
ds = InputBox("Type in reporting date as YYYY-MM-DD", "reporting date")
exestr = "Rterm --restore --save < m.in.R > out.txt --args " & ds
ret = Shell(exestr) ' opens Rterm and does not source the script
*************
if I put the string in a batch file aaa.bat and use

ret=Shell(aaa.bat)

then redirection works, but I lose the return value from the process. ret
simply reports whether cmd started successfully.
I need to have the value returned from Rterm, which is 0 upon successful
finish and -1 otherwise.

Thank you all

 

[Rick Rothstein \(MVP - VB\)]

Redirection, like non-built-in DOS function calls, need to call the command processor in order to work. Try this Shell statement instead and see if it works...

ret = Shell(Environ$("comspec") & " /c " & exestr)

Rick