Call to Powershell dir function not allowing more than 1 wildcard

[easperhe29]

All,

I can't get the power shell dir function to work with more than one wildcard through excel vba. If I have this directory structure:

C:\Test1\Test2\Sample1.xlsm
C:\Test1\Test2\Sample2.xlsm

This VBA call to power shell will return 2 files

fileRegExp = "C:\Test\Test2\*.xlsm"
files = Split(CreateObject("WScript.Shell").Exec("cmd /c dir " & """" & fileRegExp & """" & " /b").StdOut.ReadAll, vbCrLf)


But all these other options don't return any files

fileRegExp = "C:\Test\*\*.xlsm"
fileRegExp = "C:\Test\Test?\*.xlsm"
files = Split(CreateObject("WScript.Shell").Exec("cmd /c dir " & """" & fileRegExp & """" & " /b").StdOut.ReadAll, vbCrLf)

For somereason, it seems like only one wildcard is accepted. But in powershell itself, all of these work fine. Any ideas??

 

[John_w]

The cmd in that code runs the old DOS shell (command prompt), not PowerShell. DOS allows wildcards only in the file name, not the folder name.

The PowerShell version would be:

    files = Split(CreateObject("WScript.Shell").Exec("powershell.exe -command dir """ & fileRegExp & """").StdOut.ReadAll, vbCrLf)

But note I've omitted the /b (bare listing) switch because it isn't supported in PS - you'll have to find out the equivalent.