When VBA isn’t VBA

How to list all files in all subfolders using VBA…

This is something that I see asked quite a lot on various forums, and I’ve seen lots of different attempts using recursion and the file system object to name but a few. I’m going to make it really simple for you – it looks like this:

files = Filter(Split(CreateObject("WScript.Shell").Exec("CMD /C DIR ""%USERPROFILE%\Desktop\*.*"" /S /B /A:-D").StdOut.ReadAll, vbCrLf), ".")

Okay, so that might not look simple (and what the hell is that smiley face in the middle of it?). That’s because it’s not technically VBA.


Okay – so it is VBA, but it’s actually running a command through cmd.exe and reading the output – so it’s kind of not VBA…

The point I’m making here is that whilst VBA is predominantly restricted to it’s MS Office environment, you can still use it to control other applications.

“So how does this work?”

Well to explain, let’s break it down by each step:

  • CreateObject("WScript.Shell")...
    This creates the WScript object and invokes the WshShell object which is used to create a whole manner of things as far as programming goes.
  • ...("WScript.Shell").Exec("...
    Next, we call the Exec() method – this is used to create the CMD object, but more importantly uses the WshScriptExec object which allows us to read the standard output stream (StdOut).
  • ...").StdOut.ReadAll
    This gets the standard output stream and…well… reads it all! This outputs a string which we can then assign to a variable, display, or manipulate.

Inbetween all this is a DIR command:

DIR "%USERPROFILE%\Desktop\*.*" /S /B /A:-D

This command is used in cmd.exe to create a directory listing, the random letters shown afterwards are various flags that affect the output (in particular, the “/S” flag means look in all subdirectories). More information on the DIR command can be found here.

%USERPROFILE% is a windows environment variable that can be used within cmd.exe to represent a directory path.

The two final steps are to convert the output into an array:

  • Split( ... , vbCrLf)
    _The Split() method takes a string, and splits it into an array based on a given delimiter. In this case, we’re using vbCrLf which is one of the vb new line constants – in this case Carriage return Line feed.
  • Filter( ... , ".")
    _This method allows us to filter a single-dimension array based on a filter criteria – in this case we only want any of the entries containing a dot. This is because the StdOut string often leaves a trailing new line which we don’t want. A file should have a dot before the file’s extension and so these are the only entries we are interested in.

So finally, we are left with a variable (files) which now contains an array of all files in all subfolders in the given directory.

For a full example paste this code into a module, edit the filePath and run:

    Sub MM()
    Const filePath As String = "C:\Users\MM\*.*" '// Change as required
    Dim files As Variant

    files = Filter(Split(CreateObject("WScript.Shell").Exec("CMD /C DIR """ & filePath & """ /S /B /A:-D").StdOut.ReadAll, vbCrLf), ".")

    For Each file In files
        Debug.Print CStr(file)

    End Sub

Before you start…

Please keep your “doesn’t work if the directory is empty or there’s only one file or blah blah…” style comments to yourself – this code is a simply a proof of concept, nothing more, nothing less. It isn’t a real-world solution so if you want to use it to actually do something then tweak it to how you see fit. If you’re really stuck then you can get hold of me using the contact page.

All queries will be prioritised by the amount of beer being offered.

– Macro Man

Leave a Reply

Your email address will not be published. Required fields are marked *