How do I launch MS-DOS prompt to execute a batch file from within Excel worksheet with /VBA?

[veritasca]

I am creating a batch file with MS-DOS commands within an Excel
worksheet using VBA. Now, I have to execute this batch file. I don't
know how to launch the MS-DOS prompt and then write out the command to
execute the batch file. Any ideas would be appreciated. Thanks.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Dave Peterson]

Take a look at Shell in VBA's help.

I am creating a batch file with MS-DOS commands within an Excel
worksheet using VBA. Now, I have to execute this batch file. I don't
know how to launch the MS-DOS prompt and then write out the command to
execute the batch file. Any ideas would be appreciated. Thanks.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Earl Kiosterud]

Veritasca,

You've asked for a command prompt from VBA. You probably don't need to do
that in order to run your batch file. The command prompt is a user
interface where you tell windows to run a program. You type its name at the
prompt. If you really want it, use Shell, as Dave said, launching cmd.
That's the name of the command prompt program:

Shell "cmd", vbMaximizedFocus

In some older Windows versions, it was "command" (as it was in DOS).

But you can run your batch program from VBA automatically, without a command
prompt. Just use the name of your batch file:

Shell "a.bat" ' or
Shell "a.bat", vbMaximizedFocus

 

[veritasca]

OK, thanks. I did try the Shell command and it works fine. How do you
keep the MS-DOS window active and visible?
This is my statement:

TaskID = Shell(Filename, 1)

1 = vbNormalFocus

Also, a sidetrack here, I tried the Search box at the top of this forum
and it always returns "no match" no matter what I type in!
Is there something I am doing wrong here??
Thanks!

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Earl Kiosterud]

V,

The window closes when your batch file ends. Maybe that's why you're not
seeind it. Try a PAUSE in the batch file for a while. What do you want to
see in the window? A prompt? Your batch program running?

I don't know about the search in the forum you're using. Consider setting
up an account with msnews.microsoft.com (in OE, it's Tools, Accounts). It's
not really intended to be part of the worldwide newsgroup community
(USENET).

 

[Dave Peterson]

Another way (from a previous post):

Shell Environ("comspec") & " /c c:\test.bat", vbNormalFocus

(/k instead of /c is nice for testing. It keeps the DOS window open.)

 

[veritasca]

Hi,

I tried your command using my batch file:
Shell Environ("comspec") & " /k C:\Data Integration\Upload_Utility.bat",
vbNormalFocus

but when the MS-DOS window is displayed, it shows this error:
'C:\Data' is not recognized as an internal or external command,
operable program or batch file.

Any ideas why it didn't execute?
When I tried it the simple way I listed above, it seems to work.



** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Alan]

Hi,

I tried your command using my batch file:
Shell Environ("comspec") & " /k C:\Data
Integration\Upload_Utility.bat", vbNormalFocus

but when the MS-DOS window is displayed, it shows this error:
'C:\Data' is not recognized as an internal or external command,
operable program or batch file.

Any ideas why it didn't execute?
When I tried it the simple way I listed above, it seems to work.

You might try this:

Shell Environ("comspec") & " /k C:\DataIn~1\Upload_Utility.bat",
vbNormalFocus

You could also try re-naming 'Upload_Utility.bat' to conform to
normal 8.3 filename format.

HTH,

Alan.

 

[Earl Kiosterud]

V,

Comspec refers to cmd.exe, including the complete path. Cmd is the command
interpreter. That's the program that puts a prompt ("C:\" on the screen and
waits for the user to enter a command (a program to be run). It appears you
want to launch the command interpreter, and get it to run your program.

I don't think you need the command interpreter to do this. Why don't you
just use Shell to run your program? It will run in a window.

Shell "C:\Data Integration\Upload_Utility.bat", vbNormalFocus

I think this will work, even with a long file name.

Or, if you need to use cmd, try this (also untested):

Shell Environ("comspec") & " /k ""C:\Data Integration\Upload_Utility.bat""",
vbNormalFocus

 

[Earl Kiosterud]

Oops. I didn't realize I was jumping into Dave's thread. I was addressing
the OP's original desire to launch the command interpreter, and that it
might not be necessary to do so.

 

[veritasca]

OK, I did as you suggested and it worked fine. Thanks a bunch!

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins &
Software!http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Dave Peterson]

Jumping into a thread isn't a problem for me. <vbg> I hope it isn't a problem
for you.

The benefits (corrections/clarifications/or just different ideas) are very
nice--not counting the timing issues.
I wouldn't have gotten to the reply until later and your response made for one
happy OP.