Running a Batch file from VBA

[D.riggins]

I am trying to run a batch file from Excell 2003 using the Shell function in
VBA and I can’t seem to get it to work. The code is like this:

RetVal = Shell(BatFilePath, vbNormalFocus)

Where BatFilePath is a string variable with the full path of the batch file
(e.g., D:\...\Batchfile.bat)

The line executes – I get a value in the RetVal variable and I see a brief
flash of the command prompt window, but I do not see the file that the batch
file is supposed to produce. A search of my hard drives indicates that the
results have not been created in some unexpected location.

The batch file works properly when run manually.

 

[Rick Rothstein]

In older version of Windows (I thought this was changed in newer versions,
but I haven't used Shell in ages, so I don't know for sure), you used to
have to encase any path/filenames where either the path or filename
contained internal spaces. You could try this and see if it works...

RetVal = Shell("""" & BatFilePath & """", vbNormalFocus)

Another possibility is that the briefly flashing command window is
displaying an error message of some kind. I believe this line of code will
leave the command window up so you can read its contents...

RetVal = Shell(Environ$("comspec") & " /k " & """" & _
BatFilePath & """", vbNormalFocus)

 

[Jacob Skaria]

Dim strSheet as String
strSheet = Sheets("info").Range("F30")
Sheets(strSheet).Activate

OR

Dim strSheet as String
strSheet = Sheets("info").Range("F30")
Application.Goto Reference:=Sheets(strSheet).Range("A1")

 

[Jacob Skaria]

Oops ..the below is a wrong post; please ignore..

 

[Steve Yandl]

Keep in mind, a batch file isn't an executable file. When run from a
console (dos type) window or in the context of the Windows operating system
environment, it is "hosted" by either cmd.exe or command.com. The Shell
function doesn't check the Windows registry to determine the program needed
to launch a file with the extension "bat". The solution is to use the Shell
function to start either cmd.exe or command.com (or use %comspec% for more
versatility as in Rick's response earlier) and then present the full path to
your batch file as the argument to %comspec%.


Steve Yandl

 

[D.riggins]

Thanks. Having the command window stay open helped. The command window was,
in fact showing an error message. It turns out that the batch file was being
executed at the root of the D:\ drive and not in the subdirectory of the D
drive where the batch file is stored. As a result the batch file could not
find the other files it was supposed to process (the batch file simply
concatenates several MP3 files located in the same directory as the batch
file using the copy command).

For now I've taken the brute force approach and changed the batch file so it
explicitly changes the directory before processing the rest of the commands
in the batch file. I'm wondering, though, if there is not a more elegant
means of doing this using the Environ command or some other command.

 

[PNK]

My problem is identical to the one mentioned here and the advice about
keeping the window open definitely helped. Any suggestions as to how one can
change the starting directory of the shell or command prompt so that the
default directory is the local directory instead of the D:\ drive.

I'm a novice to programming and I'm sure I haven't used all the right
terminology but I hope I was able to convey my point. Any help would be most
Appreciated.

Regards,