dos from excel vba?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi, is there any way that i could open and enter commands in ms dos
controlling from excel vba code?, like, say i wanted to change the current
directory in msdos from d: to c:, is there anyway that i could do it from
excel vba? if so, could you give me a hint..
 
In general, you can use Shell to issue DOS commands--even call other programs.

But VBA has lots of stuff to work with files and folders built in.

Including
chdrive (to change drives)
chdir (to change folders)




dim OldPath as String
dim NewPath as string

OldPath = CurDir
newpath = "D:\somefolder"
chdrive newpath
chdir newpath

'do a lot of work and change back

chdrive oldpath
chdir oldpath
 
thanks for your reply, but the thing is, i need to run a program in msdos, by
changing its current directory and running an exe file, and i was hoping to
do it from a userform in vba..
 
i mean, i see that you could run msdos application by using the shell, but
could you pass any parameter to the application?
 
Something like:

Option Explicit
Sub testme()

Dim myBatFile As String
Dim myStr As String
Dim myCommand As String

myBatFile = "C:\my documents\excel\myfile.bat"
myStr = "hello"

myCommand = Chr(34) & myBatFile & Chr(34) & " " & myStr

Shell Environ("comspec") & " /k " & myCommand, vbMaximizedFocus

End Sub

Change the /k to /c to dismiss that DOS window when it's done.
 
I am really sorry for troubling you, but i think i am getting closer but not
there yet. :(

the coding i used is:

Private Sub UserForm_Click()
Dim RetVal
Dim myBatFile As String
Dim myStr As String
Dim myCommand As String

myBatFile = "C:\VQ80_2\VQ80RUN"

' VQ80RUN is the program that i wanted to run in dos

myStr = TextBox1.Text & ".prn" & " " & TextBox1.Text & ".out"
'mystr holds the parameters to be used in the program VQ80RUN.

myCommand = Chr(34) & myBatFile & Chr(34) & " " & myStr

'i think the problem i find is, (mycommand) is double quoting , for eg
""c:\vq80_2\vq80run" 1.prn 1.out"

i am not sure if the quotation is causing trouble or if the coding itself is
wrong..

Shell Environ("comspec") & " /k " & myCommand, vbMaximizedFocus
End Sub
 
I set up a small .bat file:

Echo %1 %3

and ran this:

Option Explicit
Sub testme()

Dim myBatFile As String
Dim myStr As String
Dim myCommand As String

myBatFile = "C:\temp\a.bat"
myStr = "hello goodbye again"

myCommand = Chr(34) & myBatFile & Chr(34) & " " & myStr

Shell Environ("comspec") & " /k " & myCommand, vbMaximizedFocus

End Sub

And I got the first and third parms echoed back.

Those double quotes around the command will eliminate any problem if there are
spaces in the folder name that holds the .bat file.

Maybe you could add an echo statement at the top to see if you're getting what
you think you should be getting.

Ahhhh.

One more thought.

If 1.prn and 1.out aren't in the current folder then you'll have trouble, too.

You can have your .bat file change to the folder or you can build your strings
so that they include a fully qualified filename--but you'll have to watchout for
double quotes <vbg>.

In the .bat file

c:\
cd \myfolder\myfolder

the rest of the .bat file here
 
hello, Dave, one last question,:), is it possible to close an ms dos window
(was opened using shell)??, if so, could you please explain to me??, thank you
 
From that earlier post:

Shell Environ("comspec") & " /k " & myCommand, vbMaximizedFocus


Change the /k to /c to dismiss that DOS window when it's done.
 
sorry if i am trying to be stupid Dave, the coding i used is

Dim RetVal
Dim OldPath As String
Dim NewPath As String
OldPath = CurDir
NewPath = "C:\VQ80_2\"
ChDrive NewPath
ChDir NewPath
b = NewPath & "VQ80RUN.bat" & " """ & TextBox1 & ".prn""" & " """ & TextBox1
& ".out"""
RetVal = Shell(b, vbMinimizedFocus)
Shell Environ("comspec") & " /c " & myCommand, vbMinimizedFocus

, the program executes and ends in the ms dos window,and promts "press any
key to continue", so the dos window closes if any key is pressed.

i did use ' Shell Environ("comspec") & " /c " & myCommand, vbMinimizedFocus,
but no effect..
 
one more thing, would it be easier to pass an other parameter after the
program has executed and stopped, i mean like when the dos prompts "press any
key to continue", is it possible to send a parameter to dos so that the
"press any key to continue" will be executed??
 
It sounds like that it's not the DOS window that's giving you that message--it
sounds like it's the application that you're running that's doing the message.

You may be able to edit that vq80run.bat to get rid of that last prompt.

If you look and don't see anything, then maybe just adding:

EXIT

as the last line of the .bat file would be enough.
 
Ps. You may want to post the version of Windows you're running. I'm not sure
if all versions behave the same.
 
i am home now, i will have to try editing the bat file tomorrow, but i think
they use windows 2000.

Dave Peterson said:
Ps. You may want to post the version of Windows you're running. I'm not sure
if all versions behave the same.
 
thanks Dave, editing the .bat file and adding the exit after the program s
done, is the simplest but effective solution, thank you very much for
spending your time on my queries..thank you

Dave Peterson said:
Ps. You may want to post the version of Windows you're running. I'm not sure
if all versions behave the same.
 
Back
Top