Solved: Run a program from VBA, and capture output

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

Quote:

---

"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:
[VBA]Sub test2()
Dim RetVal
RetVal = Shell("""c:\program files\md5deep\md5deep"" -b C:\*.txt > C:\md5results2.txt", vbNormalFocus)
End Sub[/VBA]

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.

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/

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.

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

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

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-prog...er-search.html

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.