dos from excel vba?
 

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

dos from excel vba?
 

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




Jason wrote:

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

--

Dave Peterson

dos from excel vba?
 

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

"Dave Peterson" wrote:

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




Jason wrote:

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

--

Dave Peterson

dos from excel vba?
 

i mean, i see that you could run msdos application by using the shell, but
could you pass any parameter to the application?

"Jason" wrote:

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

"Dave Peterson" wrote:

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




Jason wrote:

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

--

Dave Peterson

dos from excel vba?
 

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.



Jason wrote:

i mean, i see that you could run msdos application by using the shell, but
could you pass any parameter to the application?

"Jason" wrote:

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

"Dave Peterson" wrote:

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




Jason wrote:

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

--

Dave Peterson


--

Dave Peterson

dos from excel vba?
 

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

dos from excel vba?
 

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

Jason wrote:

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

--

Dave Peterson

dos from excel vba?
 

those .bat commands would look more like:

C:\
cd "C:\VQ80_2\VQ80RUN"



Dave Peterson wrote:

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

Jason wrote:

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

--

Dave Peterson

--

Dave Peterson

dos from excel vba?
 

thank you very much Dave, its working now, :)

"Dave Peterson" wrote:

those .bat commands would look more like:

C:\
cd "C:\VQ80_2\VQ80RUN"



Dave Peterson wrote:

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

Jason wrote:

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

--

Dave Peterson

--

Dave Peterson

dos from excel vba?
 

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

"Dave Peterson" wrote:

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




Jason wrote:

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

--

Dave Peterson

dos from excel vba?
 

From that earlier post:

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


Change the /k to /c to dismiss that DOS window when it's done.

Jason wrote:

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

"Dave Peterson" wrote:

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




Jason wrote:

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

--

Dave Peterson


--

Dave Peterson

dos from excel vba?
 

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


"Dave Peterson" wrote:

From that earlier post:

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


Change the /k to /c to dismiss that DOS window when it's done.

dos from excel vba?
 

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

"Jason" wrote:

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


"Dave Peterson" wrote:

From that earlier post:

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


Change the /k to /c to dismiss that DOS window when it's done.

dos from excel vba?
 

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.



Jason wrote:

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

"Dave Peterson" wrote:

From that earlier post:

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


Change the /k to /c to dismiss that DOS window when it's done.


--

Dave Peterson

dos from excel vba?
 

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

Dave Peterson wrote:

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.

Jason wrote:

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

"Dave Peterson" wrote:

From that earlier post:

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


Change the /k to /c to dismiss that DOS window when it's done.


--

Dave Peterson

--

Dave Peterson

dos from excel vba?
 

i am home now, i will have to try editing the bat file tomorrow, but i think
they use windows 2000.

"Dave Peterson" wrote:

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

Dave Peterson wrote:

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.

Jason wrote:

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

"Dave Peterson" wrote:

From that earlier post:

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


Change the /k to /c to dismiss that DOS window when it's done.


--

Dave Peterson

--

Dave Peterson

dos from excel vba?
 

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" wrote:

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

Dave Peterson wrote:

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.

Jason wrote:

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

"Dave Peterson" wrote:

From that earlier post:

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


Change the /k to /c to dismiss that DOS window when it's done.


--

Dave Peterson

--

Dave Peterson