Running .BAT file from within a macro...

T

Trevor

Hello all,

I am wondering if it is possible to run a .BAT file from
within a macro in excel. Basically, the .BAT
file "creates" the text file that my macro imports. I
want, as the first step in the macro, to run the .BAT file
and then continue with the text import. As it is now, I
have to leave excel, manually run the .BAT file, and then
run the macro to import the text file. Any suggestions
would be greatly appreciated.

Thanks,

Trevor
 
S

shockley

Trevor,

Shell ("myDir\myFile.bat")

Make sure vba is in the right current drive to run the file (use

ChDrive myDrive

to change to the drive that contains the .bat file).

Also note that Shell commands run asynchronously, so if you need the results
of the .bat operation for a subsequent vba operation, it's possible to have
timing problems, i.e., the .bat operation has to finish before you get to
the vba command that uses the .bat results.

HTH,
Shockley
 
K

kkknie

Use:

Shell ("C:\BatFile.bat")

You may need to wait a few moments to let it complete. Something
like:

Dim datWait as Date

datWait = Now() + 3/24/60/60

Do Until Now() > datWait
Loop


K
 
G

Guest

That worked great. Thank you. One quick question. When
that file finishes running( it takes about two seconds ),
it leaves the cmd.exe window open in the background. Do
you know of a way to send a command to close that window?
It is simply waiting for the "Enter" key. I appreciate
your help.

Trevor
 
J

jaf

Shell Environ("comspec") & " /k myDir\myFile.bat", vbNormalFocus

/k kills (closes) the window /c leaves it open.


--
John
johnf 202 at hotmail dot com


| That worked great. Thank you. One quick question. When
| that file finishes running( it takes about two seconds ),
| it leaves the cmd.exe window open in the background. Do
| you know of a way to send a command to close that window?
| It is simply waiting for the "Enter" key. I appreciate
| your help.
|
| Trevor
|
| >-----Original Message-----
| >Trevor,
| >
| >Shell ("myDir\myFile.bat")
| >
| >Make sure vba is in the right current drive to run the
| file (use
| >
| >ChDrive myDrive
| >
| >to change to the drive that contains the .bat file).
| >
| >Also note that Shell commands run asynchronously, so if
| you need the results
| >of the .bat operation for a subsequent vba operation,
| it's possible to have
| >timing problems, i.e., the .bat operation has to finish
| before you get to
| >the vba command that uses the .bat results.
| >
| >HTH,
| >Shockley
| >
| >
| message
| >| >> Hello all,
| >>
| >> I am wondering if it is possible to run a .BAT file from
| >> within a macro in excel. Basically, the .BAT
| >> file "creates" the text file that my macro imports. I
| >> want, as the first step in the macro, to run the .BAT
| file
| >> and then continue with the text import. As it is now, I
| >> have to leave excel, manually run the .BAT file, and
| then
| >> run the macro to import the text file. Any suggestions
| >> would be greatly appreciated.
| >>
| >> Thanks,
| >>
| >> Trevor
| >
| >
| >.
| >
 
Top