Excel VBA Macro Running DOS Application !!

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" <monir@discussions.microsoft.com> wrote in message
news:0702E150-6363-4C41-A393-183B3E260A98@microsoft.com...
> 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" <monir@discussions.microsoft.com> wrote in message
> news:0702E150-6363-4C41-A393-183B3E260A98@microsoft.com...
> > 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" <monir@discussions.microsoft.com> wrote in message
> > news:0702E150-6363-4C41-A393-183B3E260A98@microsoft.com...
> > > 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" <monir@discussions.microsoft.com> wrote in message
> > > news:0702E150-6363-4C41-A393-183B3E260A98@microsoft.com...
> > > > 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" <monir@discussions.microsoft.com> wrote in message
> > > > news:0702E150-6363-4C41-A393-183B3E260A98@microsoft.com...
> > > > > 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
> > > > > >
> > > > > >
> > > >
> > > >
> > > >