Application.FileSearch replacement

[remeng]

Hi All,

I need to search a folder and sub folders for a file name containing some value. In the past it looked like Application.FileSearch was a valid way to get the list. Now, Application.FileSearch looks to have been obsoleted in the early 'ots.

Is there an updated method? I've looked at google but most articles are older than 2010 and the methods no longer work.

Since this is for excel, I'd like the results to record into a dynamic table. I'll need this list for other operations and the dynamic table will be easier to use.

Any recommendations would be helpful.

Thanks!

 

[Andrzejek]

Here is some code to search a folder and sub-folders for *.txt and *.pdf files, but you can modified it to fit your needs:

Option Explicit
Dim R As Integer

Sub FolderDrillDown()
'set a reference to the Microsoft Scripting Runtime Library
    Dim oFSO As FileSystemObject
    Dim oFolder As Folder
    Dim oFLDR  As Folder
    Dim oFile As File
    Dim s As String, folderspec As String
    
    Sheet1.Cells.ClearContents
    
    folderspec = "C:\Andy"     '<<<MODIFY THIS PATH
    Sheet1.Range("A1:B1").Value = Array("Path", "File Name")
    R = 2
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    
    Set oFolder = oFSO.GetFolder(folderspec)
    
    For Each oFile In oFolder.Files
        Select Case Split(oFile.Name, ".")(1)
            Case "txt"
                'Debug.Print oFolder.Path & "\" & oFolder.Name, oFile.Name
                Sheet1.Range("A" & R & ":B" & R).Value = Array(oFolder.Path & "\" & oFolder.Name, oFile.Name)
                R = R + 1
            Case "pdf"
                'Debug.Print oFolder.Path & "\" & oFolder.Name, oFile.Name, "<<"
                Sheet1.Range("A" & R & ":C" & R).Value = Array(oFolder.Path & "\" & oFolder.Name, oFile.Name, "<<")
                R = R + 1
        End Select
    Next
    
    GetSubFolder oFolder
    
    Call MsgBox("I am DONE.", vbOKOnly, "R U Happy Now?")
    
End Sub

Sub GetSubFolder(oFLDR As Folder)
    Dim oFolder  As Folder
    Dim oFile As File
        
    For Each oFolder In oFLDR.SubFolders
        For Each oFile In oFolder.Files
            Select Case Split(oFile.Name, ".")(1)
                Case "txt"
                    'Debug.Print oFLDR.Path & "\" & oFolder.Name, oFile.Name
                    Sheet1.Range("A" & R & ":B" & R).Value = Array(oFLDR.Path & "\" & oFolder.Name, oFile.Name)
                    R = R + 1
                Case "pdf"
                    'Debug.Print oFLDR.Path & "\" & oFolder.Name, oFile.Name, "<<"
                    Sheet1.Range("A" & R & ":C" & R).Value = Array(oFLDR.Path & "\" & oFolder.Name, oFile.Name, "<<")
                    R = R + 1
            End Select
        Next
        
        GetSubFolder oFolder
    Next
     
End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

After Excel 2003 FileSearch was deactivated.
Some replacements, incl. subfolders search and FileSearch encapsulated in class module, you can find here (and in parent topic).

combo

 

[remeng]

Thanks Andy. That allowed me to find this video which significantly shortened the code.

Link

 

[Andrzejek]

The video is nice, but it does just the specified folder.
What about searching any sub-folders?


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[remeng]

Andy,

I am writing code so that it indexes the sub folder list and then tacks it onto the folder pathway at the end so it can start the search over again with a loop statement.

 

[strongm]

>at the end so it can start the search over again with a loop statement

And if the subfolders have subfolders?

Typically (but not exclusively) we would consider recursion for this. A google for 'recursion folder vba' should get a lot of hits ...

 

[Andrzejek]

Set your own [red]text to search for[/red] and [red]initial folder[/red] to start from:

Option Explicit
Dim R As Integer

Sub test()
Call FindFilesWith([red]"Cont", "C:\Andy"[/red])
End Sub

Private Sub FindFilesWith(ByRef strF As String, ByRef strPath As String)
   [green] 'set a reference to the Microsoft Scripting Runtime Library[/green]
    Dim oFSO As FileSystemObject
    Dim oFolder As Folder
    Dim oFLDR  As Folder
    Dim oFile As File
    Dim folderspec As String
    
    Sheet1.Cells.ClearContents
    
    folderspec = strPath
    Sheet1.Range("A1:B1").Value = Array("Path", "File Name")
    R = 2
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    
    Set oFolder = oFSO.GetFolder(folderspec)
    Call WriteToSheet(oFolder, oFile, strF)
    Call FinFilesInSubFolder(oFolder, strF)
    Sheet1.Columns("A:B").EntireColumn.AutoFit
    Call MsgBox("I am DONE.", vbOKOnly, "R U Happy Now?")

End Sub

Private Sub FinFilesInSubFolder(oFLDR As Folder, ByRef strF As String)
    Dim oFolder  As Folder
    Dim oFile As File
        
    For Each oFolder In oFLDR.SubFolders
        Call WriteToSheet(oFolder, oFile, strF)
        Call FinFilesInSubFolder(oFolder, strF)
    Next
     
End Sub

Private Sub WriteToSheet(ByRef oFolder As Folder, ByRef oFile As File, ByRef strF As String)

For Each oFile In oFolder.Files
    If InStr(UCase(oFile.Name), UCase(strF)) Then
        Sheet1.Range("A" & R & ":B" & R).Value = Array(oFolder.Path & "\", oFile.Name)
        R = R + 1
    End If
Next

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

remeng,
Did any of the suggestions here help you?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[remeng]

Hi Guys,

Sorry for going dark on this thread. Yes I got it working.

Now I have another issue.

To index all the file names in the root older and sub folder in my sandbox takes about 4 minutes. There are currently about 26,000 files that it goes through.

I've been informed that in the production folders, out of 13 folders, 1 folder alone has 180k files.

Obviously, this is not really an effective method if I want this done quickly.

Does anyone know a way to get all the file names in the folder like you can in cmd prompt and import it to Excel? Even generating a "print" file in cmd takes about 20 minutes.

I'm open to any ideas.

Thanks all!

PS: this new layout is freaking me out

 

[Andrzejek]

13 folders with 180,000 each, that would make your app run for about 6 hours

Aside from coding, could you paint a picture of what needs to be accomplished, what do anybody needs this functionality for? Hopefully these 13 folders are NOT filled with 1000's of files on regular basis.

Maybe, write the file names to a data base daily (just the new files, which - hopefully - is not in the 1000's) and query the table instead?

 

[remeng]

Hi Andy,

We have 13 different folders with prints in them. The same part number drawing can be in multiple folder just the file name is different. The file name consists of the part number, rev letter, and status of the print.

My macro is designed to search for all the first level folders and the root folder for a list of PNs and return the entire file names that contain the PN in them.

The macro then determines of the results, which file should be used.

So far, the entire program works, it's just "slow".

When the sum of files in the folders contains 5k files or less, it processes very quickly and there isn't any issue.

After about 25k or more files in all folders combined, it starts going very slow; about 4 minutes to complete the search. The time is very consistent.

The task manager doesn't see any spikes in memory or processor usage. This doesn't allow a user to know if there is something running in the background either. Excel just becomes "unresponsive" even though it is in fact working.

All tables are using dynamic tables*

Process:

1) User selects a .txt file to load the PN to be searched. The info goes into the first dynamic table.
2) Macro initiated
3) Root folder is selected by the user
4) Macro indexes the folders in the root folder
5) All PN are search for in the root folder before moving to the next folder. All the file names that contain the PN being searched are recorded into a dynamic table
6) When a file name is located, the different file name parts are broken into different columns in the table. VBA handles the formulas, and they are not processes in the worksheet.
7) The process continues for all the sub folders
8) After the list of file names and attributes are compiled, the newest file that meets the criteria is recorded into another table
9) The user then has the option to print the PDF files to a local printer

The slowdown is somewhere between op 5 and 7. When I use debug.print the process just hangs and wont record anything during the 10k+ folder. It starts printing when it hits a folder with less PDFs in it, but it doesn't include anything from the larger folder.


With the 25k - 26k number of files in the testing sandbox, it is consistently 4 minutes to process.

In the live environment, a single folder contains 180k of files.

Since these are used globally, I cannot alter the file names or the folder structure.

 

[Andrzejek]

So it looks like your code and logic works, it just takes a LOT longer in production than it takes you in your 'sandbox' because of the number of files. Is that correct?

How about my original suggestion:

Maybe, write the file names to a data base daily (just the new files, which - hopefully - is not in the 1000's) and query the table instead?

 

[remeng]

Hi Andy,

The sandbox has 26k files = 4 minutes
Production can have 250k+ files = ??? minutes / hrs. / years?

Is there a "quick" way to get a database index inside Excel so I can get away from a live folder search?

When I use cmd prompt as a comparison to just try and index production, it takes about 2 hours.

 

[remeng]

I did find this for cmd prompt which seems to be near instantaneously. This is good for a single folder at a time. I'd need to convert this for every sub folder though.

Thoughts?

dir FOLDER DIRECTOR PATH /b /a-d  > "C:\print.txt"

 

[remeng]

I've tried to run the cmd code in VBA, but it isn't able to run the command. It will open command prompt, but it won't execute the code.

I've tried VBA.SHELL and SHELL. There aren't any other articles that I can find that would indicate that this is supported.

Here is an example of one of the things I've tried.

Shell "cmd.exe /k dir q: /b /a-d > C:\FOLDER PATHWAY\vba print.txt", vbNormalFocus

 

[remeng]

ok - got it working. What a pain in the butt!

Sub live_working_code()

str_to_pass = "dir FOLDER PATHWAY /b /a-d > ""TARGET FOLDER PATHWAY\vba print long.txt"""


Call Shell("cmd.exe /s /k " & str_to_pass, vbNormalFocus)

End Sub

the "cmd.exe /s /k" instructs command prompt to receive the next commands.

So for instance:

Call Shell("cmd.exe /s /k " & str_to_pass, vbNormalFocus) will open the command prompt and execute the DIR folder pathway command.