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
>
>

monir,
Have you tried enclosing the whole command string in "" ?

NickHK

"monir" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >

NickHK;
Yes, I've tried "". Didn't work.
Here's a summary:

Sub Test3()
'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
'In this sample macro, I'm trying to immulate the DOS command statement:
'C:\MacroToRunDOS>LL107 <myInpFile >myOutFile (return) which works fine when
typed in DOS
'specify the names of the input & output files and the full path
' myPath = "C:\MacroToRunDOS
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

A successful run of the above macro (or similar) should produce the results
of LL107 in myOutFile in myPath folder.

The above macro produces No error and No output file!!
Still working on it. Haven't given up yet!
Regards.


"NickHK" wrote:

> monir,
> Have you tried enclosing the whole command string in "" ?
>
> NickHK
>
> "monir" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > >
> > >
>
>
>

Hello;
It's clear now that the re-direct characters "<" and ">", which work fine
(in DOS environment when typed in a Command Prompt window to re-direct input
and output files, e.g.; C:\MacroToRunDos>LL107 <LL107_575123_93.inp
>LL107_575123_93.out )
would not work if the characters are included in a VBA SHELL, either
directly [as in Sub Test3() below], or indirectly (as in a batch file that's
opened in SHELL, as in Sub Test6() below].

Sub Test3()
'specify the names of the input & output files and the full path
' myPath = "C:\MacroToRunDOS
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

Sub Test6()
'It creates and saves the Batch File mylist.bat correctly in myPath folder.
'specify the names of the input & output files and the full path
' myPath = "C:\MacroToRunDOS
myInpFile = "LL107_" & Range("F19") & Range("G19") & ".inp"
myOutFile = "LL107_" & Range("F19") & Range("G19") & ".out"
myPath = ThisWorkbook.Path
Filenr = FreeFile()
Open myPath & "\mylist.bat" For Output As #Filenr
Print #Filenr, myPath & "\LL107.exe" & " <" & myInpFile & " >" & myOutFile
Close #Filenr
Shell myPath & "\mylist.bat"
'RSP = Shell(Environ$("COMSPEC") & " /C" & myPath & "\mylist.bat",
vbNormalFocus)
End Sub

The above 2 sample macros produce NO errors, and completely ignore the SHELL
arguments !!

I thought by providing the above, it might trigger a suggestion or two from
you the experts. Thank you.

"monir" wrote:

> NickHK;
> Yes, I've tried "". Didn't work.
> Here's a summary:
>
> Sub Test3()
> '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
> 'In this sample macro, I'm trying to immulate the DOS command statement:
> 'C:\MacroToRunDOS>LL107 <myInpFile >myOutFile (return) which works fine when
> typed in DOS
> 'specify the names of the input & output files and the full path
> ' myPath = "C:\MacroToRunDOS
> 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
>
> A successful run of the above macro (or similar) should produce the results
> of LL107 in myOutFile in myPath folder.
>
> The above macro produces No error and No output file!!
> Still working on it. Haven't given up yet!
> Regards.
>
>
> "NickHK" wrote:
>
> > monir,
> > Have you tried enclosing the whole command string in "" ?
> >
> > NickHK
> >
> > "monir" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > > >
> > > >
> >
> >
> >

Hello;

Sub Test6()
'It creates and saves the Batch File mylist.bat correctly in myPath folder.
'specify the names of the input & output files and the full path
' myPath = "C:\MacroToRunDOS
myInpFile = "LL107_" & Range("F19") & Range("G19") & ".inp"
myOutFile = "LL107_" & Range("F19") & Range("G19") & ".out"
myPath = ThisWorkbook.Path
Filenr = FreeFile()
Open myPath & "\mylist.bat" For Output As #Filenr
Print #Filenr, myPath & "\LL107.exe" & " <" & myInpFile & " >" & myOutFile
Close #Filenr
Shell myPath & "\mylist.bat"
End Sub

You may find the following experiment really interesting.
1. Sub Test6 () created and saved mylist.bat as:
C:\MacroToRunDOS\LL107.exe <LL107_575123_93.inp >LL107_575123_93.out
2. I clicked the shortcut to open the Command Prompt window
3. Changed the displayed directory to myPath:
C:\MacroToRunDos>
4. Typed mylist at the prompt
5. The following lines were displayed, and the program ran successfully !!!:
C:\MacroToRunDOS>C:\MacroToRunDOS\LL107.exe 0<LL107_575123_93.inp
1>LL107_575123_93.out
Execution terminated : 0 (successful run)
C:\MacroToRunDOS>

6. By typing mylist at the prompt, the system took the command line in
mylist.bat (item 1. above) and displayed it with "0" before "<", and with "1"
before ">" (item 5 above), in addition to the single space already there in
the batch file, and then ran the program successfully !!!!
Where did these digits 0 and 1 come from ??

7. Next, it was obvious to try including those digits in the Print statement
in the above macro code:

Print #Filenr, myPath & "\LL107.exe" & " <" & myInpFile & " >" & myOutFile
'Print #Filenr, myPath & "\LL107.exe" & " " & "<" & myInpFile & " " & ">" &
myOutFile
'Print #Filenr, myPath & "\LL107.exe" & " " & "0<" & myInpFile & " " & "1>"
& myOutFile
'Print #Filenr, myPath & "\LL107.exe" & " 0" & "<" & myInpFile & " 1" & ">"
& myOutFile
None worked ! Still no error and no output file.

Are we any closer ?? Regards.


"monir" wrote:

> Hello;
> It's clear now that the re-direct characters "<" and ">", which work fine
> (in DOS environment when typed in a Command Prompt window to re-direct input
> and output files, e.g.; C:\MacroToRunDos>LL107 <LL107_575123_93.inp
> >LL107_575123_93.out )
> would not work if the characters are included in a VBA SHELL, either
> directly [as in Sub Test3() below], or indirectly (as in a batch file that's
> opened in SHELL, as in Sub Test6() below].
>
> Sub Test3()
> 'specify the names of the input & output files and the full path
> ' myPath = "C:\MacroToRunDOS
> 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
>
> Sub Test6()
> 'It creates and saves the Batch File mylist.bat correctly in myPath folder.
> 'specify the names of the input & output files and the full path
> ' myPath = "C:\MacroToRunDOS
> myInpFile = "LL107_" & Range("F19") & Range("G19") & ".inp"
> myOutFile = "LL107_" & Range("F19") & Range("G19") & ".out"
> myPath = ThisWorkbook.Path
> Filenr = FreeFile()
> Open myPath & "\mylist.bat" For Output As #Filenr
> Print #Filenr, myPath & "\LL107.exe" & " <" & myInpFile & " >" & myOutFile
> Close #Filenr
> Shell myPath & "\mylist.bat"
> 'RSP = Shell(Environ$("COMSPEC") & " /C" & myPath & "\mylist.bat",
> vbNormalFocus)
> End Sub
>
> The above 2 sample macros produce NO errors, and completely ignore the SHELL
> arguments !!
>
> I thought by providing the above, it might trigger a suggestion or two from
> you the experts. Thank you.
>
> "monir" wrote:
>
> > NickHK;
> > Yes, I've tried "". Didn't work.
> > Here's a summary:
> >
> > Sub Test3()
> > '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
> > 'In this sample macro, I'm trying to immulate the DOS command statement:
> > 'C:\MacroToRunDOS>LL107 <myInpFile >myOutFile (return) which works fine when
> > typed in DOS
> > 'specify the names of the input & output files and the full path
> > ' myPath = "C:\MacroToRunDOS
> > 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
> >
> > A successful run of the above macro (or similar) should produce the results
> > of LL107 in myOutFile in myPath folder.
> >
> > The above macro produces No error and No output file!!
> > Still working on it. Haven't given up yet!
> > Regards.
> >
> >
> > "NickHK" wrote:
> >
> > > monir,
> > > Have you tried enclosing the whole command string in "" ?
> > >
> > > NickHK
> > >
> > > "monir" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > > >
> > > > >
> > >
> > >
> > >

Hello;

Here's the macro code which works perfectly and as desired, provided
there're no spaces in the folders/files names:

Sub Test7()
'Jun 23, 2006.
'This Excel VBA macro runs a DOS application with re-directed input and
output files
'The DOS program LL107.exe and the 2 re-directed files are in
ThisWorkBook.Path
'specify the names of the input & output files and the full path
' myPath = "C:\MacroToRunDOS
myPath = ThisWorkbook.Path & "\"
myInpFile = myPath & "LL107_" & Range("F19") & Range("G19") & ".inp"
myOutFile = myPath & "LL107_" & Range("F19") & Range("G19") & ".out"
Filenr = FreeFile()
Open myPath & "mylist.bat" For Output As #Filenr
Print #Filenr, myPath & "LL107.exe" & " <" & myInpFile & " >" & myOutFile
Close #Filenr
Shell myPath & "mylist.bat"
End Sub

There's a tiny problem however. When I moved the macro to the working
directory: C:\My Files\General\MacroToRunDos ,the macro did not work (no
error & no output file).
The difficulty here is associated with the "space" in the folder's name \My
Files\ in the new myPath.

According to Tom (earlier) and to MS KB articles:
"If the file name contains spaces, you will have to include it within double
quotes, e.g.;
Shell "Start ""S:\mynew project\drawing.dwg"" "
(Start: runs a Windows program or an MS-DOS program)
It's my understanding that the double quotes, or Chr(34), should work even
if there're no spaces in the names. But, I could be wrong!

Adding double quotes (by trial & error) in the above code has proved to be
problematic, and produced all kinds of errors !!

Removing the space from the folder name \My Files\ would solve the problem
locally, i.e.; the above macro would run fine, but would create other
problems associated with other applications that make reference to particular
sub-folders and/or files in \My Files\. (FORTRAN does not have equivalence to
ThisWorkBook.Path property)

Could someone please advise on how to use the double quotes in the above
code ??. Thank you kindly.


"monir" wrote:

> Hello;
>
> Sub Test6()
> 'It creates and saves the Batch File mylist.bat correctly in myPath folder.
> 'specify the names of the input & output files and the full path
> ' myPath = "C:\MacroToRunDOS
> myInpFile = "LL107_" & Range("F19") & Range("G19") & ".inp"
> myOutFile = "LL107_" & Range("F19") & Range("G19") & ".out"
> myPath = ThisWorkbook.Path
> Filenr = FreeFile()
> Open myPath & "\mylist.bat" For Output As #Filenr
> Print #Filenr, myPath & "\LL107.exe" & " <" & myInpFile & " >" & myOutFile
> Close #Filenr
> Shell myPath & "\mylist.bat"
> End Sub
>
> You may find the following experiment really interesting.
> 1. Sub Test6 () created and saved mylist.bat as:
> C:\MacroToRunDOS\LL107.exe <LL107_575123_93.inp >LL107_575123_93.out
> 2. I clicked the shortcut to open the Command Prompt window
> 3. Changed the displayed directory to myPath:
> C:\MacroToRunDos>
> 4. Typed mylist at the prompt
> 5. The following lines were displayed, and the program ran successfully !!!:
> C:\MacroToRunDOS>C:\MacroToRunDOS\LL107.exe 0<LL107_575123_93.inp
> 1>LL107_575123_93.out
> Execution terminated : 0 (successful run)
> C:\MacroToRunDOS>
>
> 6. By typing mylist at the prompt, the system took the command line in
> mylist.bat (item 1. above) and displayed it with "0" before "<", and with "1"
> before ">" (item 5 above), in addition to the single space already there in
> the batch file, and then ran the program successfully !!!!
> Where did these digits 0 and 1 come from ??
>
> 7. Next, it was obvious to try including those digits in the Print statement
> in the above macro code:
>
> Print #Filenr, myPath & "\LL107.exe" & " <" & myInpFile & " >" & myOutFile
> 'Print #Filenr, myPath & "\LL107.exe" & " " & "<" & myInpFile & " " & ">" &
> myOutFile
> 'Print #Filenr, myPath & "\LL107.exe" & " " & "0<" & myInpFile & " " & "1>"
> & myOutFile
> 'Print #Filenr, myPath & "\LL107.exe" & " 0" & "<" & myInpFile & " 1" & ">"
> & myOutFile
> None worked ! Still no error and no output file.
>
> Are we any closer ?? Regards.
>
>
> "monir" wrote:
>
> > Hello;
> > It's clear now that the re-direct characters "<" and ">", which work fine
> > (in DOS environment when typed in a Command Prompt window to re-direct input
> > and output files, e.g.; C:\MacroToRunDos>LL107 <LL107_575123_93.inp
> > >LL107_575123_93.out )
> > would not work if the characters are included in a VBA SHELL, either
> > directly [as in Sub Test3() below], or indirectly (as in a batch file that's
> > opened in SHELL, as in Sub Test6() below].
> >
> > Sub Test3()
> > 'specify the names of the input & output files and the full path
> > ' myPath = "C:\MacroToRunDOS
> > 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
> >
> > Sub Test6()
> > 'It creates and saves the Batch File mylist.bat correctly in myPath folder.
> > 'specify the names of the input & output files and the full path
> > ' myPath = "C:\MacroToRunDOS
> > myInpFile = "LL107_" & Range("F19") & Range("G19") & ".inp"
> > myOutFile = "LL107_" & Range("F19") & Range("G19") & ".out"
> > myPath = ThisWorkbook.Path
> > Filenr = FreeFile()
> > Open myPath & "\mylist.bat" For Output As #Filenr
> > Print #Filenr, myPath & "\LL107.exe" & " <" & myInpFile & " >" & myOutFile
> > Close #Filenr
> > Shell myPath & "\mylist.bat"
> > 'RSP = Shell(Environ$("COMSPEC") & " /C" & myPath & "\mylist.bat",
> > vbNormalFocus)
> > End Sub
> >
> > The above 2 sample macros produce NO errors, and completely ignore the SHELL
> > arguments !!
> >
> > I thought by providing the above, it might trigger a suggestion or two from
> > you the experts. Thank you.
> >
> > "monir" wrote:
> >
> > > NickHK;
> > > Yes, I've tried "". Didn't work.
> > > Here's a summary:
> > >
> > > Sub Test3()
> > > '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
> > > 'In this sample macro, I'm trying to immulate the DOS command statement:
> > > 'C:\MacroToRunDOS>LL107 <myInpFile >myOutFile (return) which works fine when
> > > typed in DOS
> > > 'specify the names of the input & output files and the full path
> > > ' myPath = "C:\MacroToRunDOS
> > > 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
> > >
> > > A successful run of the above macro (or similar) should produce the results
> > > of LL107 in myOutFile in myPath folder.
> > >
> > > The above macro produces No error and No output file!!
> > > Still working on it. Haven't given up yet!
> > > Regards.
> > >
> > >
> > > "NickHK" wrote:
> > >
> > > > monir,
> > > > Have you tried enclosing the whole command string in "" ?
> > > >
> > > > NickHK
> > > >
> > > > "monir" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > > > >
> > > > > >
> > > >
> > > >
> > > >

raypayette, NickHK;

This version of my earlier macro works fine, with and without spaces in the
folders/files names.
The problem of spaces in the names is solved by removing myPath from the
input, the output, and the batch files, and changing the default directory to
myPath before invoking the SHELL function.

Sub Test8()
'This Excel VBA macro runs a DOS application with re-directed input and
output files
'It creates and saves the Batch File mylist.bat in myPath folder.
'The DOS program LL107.exe and the 2 re-directed files reside in
ThisWorkBook.Path
'.name input & output files and specify working directory:
' ThisWorkBookPath is "C:\My Files\General\MacroToRunDOS
myPath = ThisWorkbook.Path & "\"
myInpFile = "LL107_" & Range("F19") & Range("G19") & ".inp"
myOutFile = "LL107_" & Range("F19") & Range("G19") & ".out"
'.create and save batch.bat file
Filenr = FreeFile()
Open myPath & "mylist.bat" For Output As #Filenr
Print #Filenr, "LL107.exe" & " <" & myInpFile & " >" & myOutFile
Close #Filenr
'.change default directory to myPath
ChDir myPath
'.use SHELL with double quotes
Shell Chr(34) & myPath & "mylist.bat" & Chr(34)
End Sub

Thank you for your help in resolving the issue.


"monir" wrote:

> Hello;
>
> Here's the macro code which works perfectly and as desired, provided
> there're no spaces in the folders/files names:
>
> Sub Test7()
> 'Jun 23, 2006.
> 'This Excel VBA macro runs a DOS application with re-directed input and
> output files
> 'The DOS program LL107.exe and the 2 re-directed files are in
> ThisWorkBook.Path
> 'specify the names of the input & output files and the full path
> ' myPath = "C:\MacroToRunDOS
> myPath = ThisWorkbook.Path & "\"
> myInpFile = myPath & "LL107_" & Range("F19") & Range("G19") & ".inp"
> myOutFile = myPath & "LL107_" & Range("F19") & Range("G19") & ".out"
> Filenr = FreeFile()
> Open myPath & "mylist.bat" For Output As #Filenr
> Print #Filenr, myPath & "LL107.exe" & " <" & myInpFile & " >" & myOutFile
> Close #Filenr
> Shell myPath & "mylist.bat"
> End Sub
>
> There's a tiny problem however. When I moved the macro to the working
> directory: C:\My Files\General\MacroToRunDos ,the macro did not work (no
> error & no output file).
> The difficulty here is associated with the "space" in the folder's name \My
> Files\ in the new myPath.
>
> According to Tom (earlier) and to MS KB articles:
> "If the file name contains spaces, you will have to include it within double
> quotes, e.g.;
> Shell "Start ""S:\mynew project\drawing.dwg"" "
> (Start: runs a Windows program or an MS-DOS program)
> It's my understanding that the double quotes, or Chr(34), should work even
> if there're no spaces in the names. But, I could be wrong!
>
> Adding double quotes (by trial & error) in the above code has proved to be
> problematic, and produced all kinds of errors !!
>
> Removing the space from the folder name \My Files\ would solve the problem
> locally, i.e.; the above macro would run fine, but would create other
> problems associated with other applications that make reference to particular
> sub-folders and/or files in \My Files\. (FORTRAN does not have equivalence to
> ThisWorkBook.Path property)
>
> Could someone please advise on how to use the double quotes in the above
> code ??. Thank you kindly.
>
>
> "monir" wrote:
>
> > Hello;
> >
> > Sub Test6()
> > 'It creates and saves the Batch File mylist.bat correctly in myPath folder.
> > 'specify the names of the input & output files and the full path
> > ' myPath = "C:\MacroToRunDOS
> > myInpFile = "LL107_" & Range("F19") & Range("G19") & ".inp"
> > myOutFile = "LL107_" & Range("F19") & Range("G19") & ".out"
> > myPath = ThisWorkbook.Path
> > Filenr = FreeFile()
> > Open myPath & "\mylist.bat" For Output As #Filenr
> > Print #Filenr, myPath & "\LL107.exe" & " <" & myInpFile & " >" & myOutFile
> > Close #Filenr
> > Shell myPath & "\mylist.bat"
> > End Sub
> >
> > You may find the following experiment really interesting.
> > 1. Sub Test6 () created and saved mylist.bat as:
> > C:\MacroToRunDOS\LL107.exe <LL107_575123_93.inp >LL107_575123_93.out
> > 2. I clicked the shortcut to open the Command Prompt window
> > 3. Changed the displayed directory to myPath:
> > C:\MacroToRunDos>
> > 4. Typed mylist at the prompt
> > 5. The following lines were displayed, and the program ran successfully !!!:
> > C:\MacroToRunDOS>C:\MacroToRunDOS\LL107.exe 0<LL107_575123_93.inp
> > 1>LL107_575123_93.out
> > Execution terminated : 0 (successful run)
> > C:\MacroToRunDOS>
> >
> > 6. By typing mylist at the prompt, the system took the command line in
> > mylist.bat (item 1. above) and displayed it with "0" before "<", and with "1"
> > before ">" (item 5 above), in addition to the single space already there in
> > the batch file, and then ran the program successfully !!!!
> > Where did these digits 0 and 1 come from ??
> >
> > 7. Next, it was obvious to try including those digits in the Print statement
> > in the above macro code:
> >
> > Print #Filenr, myPath & "\LL107.exe" & " <" & myInpFile & " >" & myOutFile
> > 'Print #Filenr, myPath & "\LL107.exe" & " " & "<" & myInpFile & " " & ">" &
> > myOutFile
> > 'Print #Filenr, myPath & "\LL107.exe" & " " & "0<" & myInpFile & " " & "1>"
> > & myOutFile
> > 'Print #Filenr, myPath & "\LL107.exe" & " 0" & "<" & myInpFile & " 1" & ">"
> > & myOutFile
> > None worked ! Still no error and no output file.
> >
> > Are we any closer ?? Regards.
> >
> >
> > "monir" wrote:
> >
> > > Hello;
> > > It's clear now that the re-direct characters "<" and ">", which work fine
> > > (in DOS environment when typed in a Command Prompt window to re-direct input
> > > and output files, e.g.; C:\MacroToRunDos>LL107 <LL107_575123_93.inp
> > > >LL107_575123_93.out )
> > > would not work if the characters are included in a VBA SHELL, either
> > > directly [as in Sub Test3() below], or indirectly (as in a batch file that's
> > > opened in SHELL, as in Sub Test6() below].
> > >
> > > Sub Test3()
> > > 'specify the names of the input & output files and the full path
> > > ' myPath = "C:\MacroToRunDOS
> > > 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
> > >
> > > Sub Test6()
> > > 'It creates and saves the Batch File mylist.bat correctly in myPath folder.
> > > 'specify the names of the input & output files and the full path
> > > ' myPath = "C:\MacroToRunDOS
> > > myInpFile = "LL107_" & Range("F19") & Range("G19") & ".inp"
> > > myOutFile = "LL107_" & Range("F19") & Range("G19") & ".out"
> > > myPath = ThisWorkbook.Path
> > > Filenr = FreeFile()
> > > Open myPath & "\mylist.bat" For Output As #Filenr
> > > Print #Filenr, myPath & "\LL107.exe" & " <" & myInpFile & " >" & myOutFile
> > > Close #Filenr
> > > Shell myPath & "\mylist.bat"
> > > 'RSP = Shell(Environ$("COMSPEC") & " /C" & myPath & "\mylist.bat",
> > > vbNormalFocus)
> > > End Sub
> > >
> > > The above 2 sample macros produce NO errors, and completely ignore the SHELL
> > > arguments !!
> > >
> > > I thought by providing the above, it might trigger a suggestion or two from
> > > you the experts. Thank you.
> > >
> > > "monir" wrote:
> > >
> > > > NickHK;
> > > > Yes, I've tried "". Didn't work.
> > > > Here's a summary:
> > > >
> > > > Sub Test3()
> > > > '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
> > > > 'In this sample macro, I'm trying to immulate the DOS command statement:
> > > > 'C:\MacroToRunDOS>LL107 <myInpFile >myOutFile (return) which works fine when
> > > > typed in DOS
> > > > 'specify the names of the input & output files and the full path
> > > > ' myPath = "C:\MacroToRunDOS
> > > > 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
> > > >
> > > > A successful run of the above macro (or similar) should produce the results
> > > > of LL107 in myOutFile in myPath folder.
> > > >
> > > > The above macro produces No error and No output file!!
> > > > Still working on it. Haven't given up yet!
> > > > Regards.
> > > >
> > > >
> > > > "NickHK" wrote:
> > > >
> > > > > monir,
> > > > > Have you tried enclosing the whole command string in "" ?
> > > > >
> > > > > NickHK
> > > > >
> > > > > "monir" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >