0

The following batch command generates a number, the count of the number of files whose size is greater than a threashold, 512K in this case, although the count is off by 1.

forfiles /P C:\ /M *.* /S /C "CMD /C if @fsize gtr 512000 echo @PATH @FSIZE"|findstr /r /n "^" |find /c ":"

How does one capture the output in VBA?

I have found an article that does the same thing but the answer seems to me is quite complicated. I wonder if there is an easier way to capture the output, like assigning an integer to the shell command?

1 Answer 1

0

You can try using the Exec method of the Shell.

eg:

  Dim WSH As WshShell
  Dim sOut As String

Set WSH = New WshShell
sOut = WSH.Exec("your_cmd_string").StdOut.ReadAll

One problem I have found with this method is that the command window stays open while the command is executing. It can be hidden, but would require windows API calls.

As a consequence, when running shell commands from VBA, I have been using the Run method with the Hidden argument, and redirecting the results to a temporary file.

8
  • "Dim WSH As WshShell" threw an error message "user defined type not defined". Do I need to include a library? I searched the net and found no article mentioning about including any library. I use Excel 2010.
    – joehua
    Commented Jan 3, 2020 at 12:11
  • @joehua Yes, you need to set a reference to Windows Script Host Object Model, although you can use late-binding if this is for distribution. Commented Jan 3, 2020 at 12:19
  • Thanks. After adding the reference, I no longer got the error message. However, when testing on a simple "dir c:\", I got another error that says "Run-time error '-2147024894 (80070002)':Method 'Exec' of object 'IWshShell3' failed." I found an article that say 8007 is an error code for Win32 but I'm running 64 bit Windows 10. Another article says this is a "file not found" error but I'm not asking the code to load any file.
    – joehua
    Commented Jan 4, 2020 at 4:24
  • @joehua I suspect there are errors in your command line. Try reading from StdErr instead of StdOut and see what's in there. You'll have problems accessing certain files with that command. Commented Jan 4, 2020 at 12:23
  • I got the exact same error when replacing 'StdOut' with 'StdErr'.
    – joehua
    Commented Jan 4, 2020 at 22:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .