Using CMD to search through network drive 1

[dvirgint]

I'm writing to see if anyone can tell me the fastest way of extracting filenames from a network drive I have offsite. I have some search criteria provided by users with the help of a userform in Access.

I've tried using FSO which in my experience in this situation is the slowest. I've also tried using CMD with WScript.Shell, which is faster, but I've recorded that it take approximately 7 minutes to perform the check with the macro. Using the Windows search bar in an explorer window provides results in about a minute or less. The parent folders I am searching through have approximately 35,000 folders each containing about 1 to 2 files.

Here is the command I use with CMD with WScript.Shell :

strTemp = CreateObject("WScript.Shell").Exec("CMD /C DIR """ & "N:\" & _
                  Me.txtRegion.Value & "\" & Me.txtYear.Value & "\" & _
                  Me.txtDossier.Value & "*.*"" /S /B /A:-D").StdOut.ReadAll

The results are then split into an array and processed.

Does anyone have any suggestions for how to improve this code?

 

[mintjulep]

VBA has a DIR() function. I would guess that staying entirely within VBA would be faster.

Might want to explore the options opened here:

http://analystcave.com/vba-dir-function-how-to-traverse-directories/

 

[dvirgint]

Looks very promising! Thanks for sharing.