Solved: Run a program from VBA, and capture output

[GreenTree]

I'd like to run a program that creates MD5 hashes of a bunch of files from VBA, redirecting the output to a file. I have what seems like it ought to be a workable solution, but something isn't working right when I try to run it from VBA. Hoping somebody here can help me!

The program is md5deep.exe, which I downloaded from Sourceforge, at http://md5deep.sourceforge.net/
The executable resides in my c:\program files\md5deep directory.

From a command prompt, I enter

"c:\program files\md5deep\md5deep" -b C:\*.txt > C:\md5results.txt
and I get what I wanted, a new text file named md5results.txt that has the hashes for every text file in the C:\ directory. Okay, let's call that same thing from VBA and life will be good, right? Not so fast!

My code is:
Sub test2()
Dim RetVal
RetVal = Shell("""c:\program files\md5deep\md5deep"" -b C:\*.txt > C:\md5results2.txt", vbNormalFocus)
End Sub

When I call that sub, I can see the flash of a DOS window, even glimpsing what appear to be hash-looking lines of characters, but no new (c:\md5results2.txt) file gets created.

(The -b flag tells the program to omit directory info from the resulting output, so file names show up as FileName.txt instead of C:\FileName.txt within the output.)

Can anybody point out what I'm doing wrong here?

Many thanks!

G.T.

[Kenneth Hobs]

What is the goal? There may be other methods that would work.

When running applications like that, one needs to add a loop to wait. While one can check file size until it doesn't change, here is a better method that waits until the process is ended.
http://support.microsoft.com/kb/q129796/

[GreenTree]

Hi Kenneth,

Thanks for your help!

At this point, I'm not even to the point of examining the file that the other program produces; I can't seem to cause the file to be generated AT ALL when running the program from VBA. Works fine when I type the command in a "Command Prompt" DOS box, but the file doesn't get generated when I run it from VBA. At least, the redirect part seems to not work; as best I can tell, the hash codes display in the DOS window for a blink of an eye before the DOS window closes, meaning the program runs, without the output going to a file.

When I use the command at the command prompt without the redirect, the hash values are output to the screen, and they're present there until I close the window; when I do it from the command prompt WITH the redirect, I see nothing on the screen {except the next command prompt}, but a file is created (not surprising). So the fact that I do see the hash values seems to indicate that the PROGRAM is running fine when I call it from VBA, just that the output is not being redirected as I expect.

Should I be doing something different to keep the Shell alive long enough to allow the redirect to the text file time to happen? I can deal with the time that the hash program takes to run by doing other time consuming things in the program before I go back to look at the results. For now, it's the lack of any results, ever, that's the problem! Should my Shell command be different in order for the " > " redirection to work?

Thanks for helping me out with this!

G.T.

[GreenTree]

Google searching finally reveals an answer:
Shell "cmd /c ""c:\program files\md5deep\md5deep"" C:\*.txt >C:\md5results2.txt", vbHide

Explained here: http://www.xtremevbtalk.com/showthread.php?t=138193

[Kenneth Hobs]

Understand that vba will continue even though the Shell process has not completed. Ergo, the reason for the link that I referenced. So, take heed if you are doing something in vba after the Shell.

For what is worth, you don't need "cmd /c" for executable files. However, you may need it if you are needing the redirect like that. I guess it doesn't hurt. Most EXE programs though allow a parameter to output their results. Since I don't know your program and you are getting what you want, it matters little.

For a code example using ExecCmd and get a sample xls, see http://www.excelforum.com/excel-programming/672632-subfolder-search.html

[GreenTree]

Your point about vba continuing on immediately is well taken, and it's something I do need to address, either with a delay before looking at the output file, or with what you've suggested. But before addressing that, I need to HAVE an output file, and that didn't seem to be happening at all. From the site I linked, it sounds like Shell by itself simply will not work with a pipeline, and the "cmd" is necessary to get the redirection to work. As you point out, Shell by itself would (and did) run the program; it just wouldn't redirect the output.

Next step will be to consider the whole question of timing, and I do appreciate your recommendations in that regard!

Best wishes,

G.T.