SubFolder search but for specific file type

Hello,

I am working with this code snippet. I am very happy with it but I need one small tweak. When I run this code it is going through over 5,000 files and many different sub folders but I really only need it to search for specific file types (mainly Solidworks files .SDDRW & SLDDRW). How would I go about being more specific with my search?

I did manage to stumble on to this code which seems to narrow down my search to a specific file but I wouldn't really know how to combine the two.

Any help is greatly appreciated.
Thank you in advance!

Sub ProcessAll(sPath As String) 
    Dim Wb As Workbook, sFile As String 
     
    sFile = Dir(sPath & "*.xls") 
     'Loop through all .xls-Files in that path
    Do While sFile <> "" 
         
        Set Wb = Workbooks.Open(sPath & sFile) 
         
         'Do something with that Workbook, insert whatever you want to do here
        Debug.Print Wb.Name 
         'You can save it, if you like, here it's not saved
        Wb.Close False 
         
        sFile = Dir 
    Loop 
End Sub

MAIN CODE

Sub ListFiles()
 
    'Set a reference to Microsoft Scripting Runtime by using
    'Tools > References in the Visual Basic Editor (Alt+F11)
   
    'Declare the variables
    Dim objFSO As Scripting.FileSystemObject
    Dim objTopFolder As Scripting.Folder
    Dim strTopFolderName As String
   
    'Insert the headers for Columns A through B
    Range("A1").Value = "File Name"
    Range("F1").Value = "Path"
   
    'Assign the top folder to a variable
    strTopFolderName = "C:\Users\Domenic\Documents"
   
    'Create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
   
    'Get the top folder
    Set objTopFolder = objFSO.GetFolder(strTopFolderName)
   
    'Call the RecursiveFolder routine
    Call RecursiveFolder(objTopFolder, True)
   
    'Change the width of the columns to achieve the best fit
    Columns.AutoFit
   
End Sub
 
Sub RecursiveFolder(objFolder As Scripting.Folder, _
    IncludeSubFolders As Boolean)
 
    'Declare the variables
    Dim objFile As Scripting.File
    Dim objSubFolder As Scripting.Folder
    Dim NextRow As Long
   
    'Find the next available row
    NextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
   
    'Loop through each file in the folder
    For Each objFile In objFolder.Files
        Cells(NextRow, "A").Value = objFile.Name
        Cells(NextRow, "B").Value = objFile.Path
        NextRow = NextRow + 1
    Next objFile
   
    'Loop through files in the subfolders
    If IncludeSubFolders Then
        For Each objSubFolder In objFolder.SubFolders
            Call RecursiveFolder(objSubFolder, True)
        Next objSubFolder
    End If
   
End Sub

Hey Trebor76,

Thank you for taking the time to put that together. It worked perfectly, I just had to tweak it from (objFile, 5) to (objFile, 6) and everything populated correctly.

Apo,

Thank you for the alternative. Your code gives me a really good insight on how I can achieve what I'm looking to do. I would have never thought to use wscript.shell. Definitely thinking outside the box. Thank you so much. I will play around with it some more.

Again, thank you both for taking the time to give me something to new to learn. Awesome!

You're welcome..

I find the Forfiles command is real good for getting specific file values into an array..

http://technet.microsoft.com/en-us/l.../cc753551.aspx

lol.. I think you just gave me a negative Rep point...

My Rep went down and there is a Red icon next to your Rep posting in my settings page..

Apo,

Oh no, I'm really sorry. How do I fix it? I'm still rather new to this Forum. I meant to give you a positive.

Apo,

Your code works really really FAST! How is that even possible? Is it because it is being structured into an array? I have to be honest, I was a little scared to run the code because of the wscript.shell (but I jumped on a friends comp and did it haha)

I really am amazed how insanely fast your code works versus mine. Unfortunately, your code is very complex and I honestly don't know how to wrap my heard around it. Any pointer and I would greatly appreciate it. My guess is because of the array property you have setup in the code but I could be mistaken and unfortunately my handle on arrays has never been good but online there are articles of how arrays can make your program run practically 60X faster.
Anyways thank you so much and any more advice I would again greatly appreciate it. Also, please let me know how I can reverse the negative mark on your record (if I have to go to admin I will). Very sorry about that (must have checked the wrong radio button because I left a good comment).

Hey apo,

I had recently found a comparison you did with forfiles vs Dir on another Forum. Could you please show me how I could use that to speed up the method you used when you introduce me to the Forfiles on here.

Private Sub CommandButton1_Click() 
     'Dir version
    Dim x, fldr As FileDialog, SelFold As String, i As Long 
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker) 
    With fldr 
        .Title = "Select a Folder" 
        If .Show <> -1 Then Exit Sub 
        SelFold = .SelectedItems(1) 
    End With 
    x = Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & SelFold & "\*.*"" /s/b").stdout.readall, vbCrLf) 
    Cells(1, 1).Resize(UBound(x)).Value = Application.Transpose(x) 
     
    For i = 1 To Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row 
        Cells(i, 1) = Split(Cells(i, 1), "\")(UBound(Split(Cells(i, 1), "\"))) 
    Next i 
    MsgBox "Done!" 
End Sub

Apo,

This definitely explains a lot. Obviously I'm still going to need to sit down and really try and dissect every line of this code (I'll see how much my ego can take after many failures but it really is the best teacher haha).

There are two questions I had when attempting a few things.

1) When I run the code on Windows using my Mac (Windows 7 is on parallels) my directory is "\\psf\User\Desktop" and the code doesn't work. Mainly just flashes the cmd and thats it. I was wondering if it has to do with the director? I ran the code at work on a server and it managed to work on unprotected files. I wonder if I'm missing something.

2) I had attempted to look for two different files by using this method *.*SLDDRW & *.*SLDPRT but I knew it was a long shot and obviously it didn't work. I was hoping you could enlighten me on how to add search for multiple files.

Again, thank you for all your help. This website http://technet.microsoft.com/en-us/l.../cc753551.aspx has diffidently open my eyes to another side of VBA.

HI.. I remember you.. the 'negative Rep point guy',lol..

It's Friday night here.. had a couple of cold ones.. but I think this is what you want?

Private Sub CommandButton1_Click()
    Dim x, fldr As FileDialog, SelFold As String, i As Long
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        If .Show <> -1 Then Exit Sub
        SelFold = .SelectedItems(1)
    End With
    x = Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & SelFold & "\*.*DDRW"" /s/b").stdout.readall, vbCrLf)
    Cells(1, 1).Resize(UBound(x)).Value = Application.Transpose(x)

    For i = 1 To Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
        Cells(i, 2) = Split(Cells(i, 1), "\")(UBound(Split(Cells(i, 1), "\")))
        Cells(i, 1) = Left(Cells(i, 1), InStrRev(Cells(i, 1), "\"))
    Next i
    MsgBox "Done!"
End Sub

HAHA yeah that's me, the negative rep guy, good memory.. It's Friday morning here, I can't wait to take down a couple of cold ones tonight!
I just did a quick run on the code, it looks good. I will need to play around with it when I have more time but was so excited I had to run it now ha-ha.

P.S is there a monetary way I can repay on this site? Your methods have helped me tremendously. I have used Forfiles for several things now and have been studying up on technet.microsoft. Such a good source!

Thanks again!

Sounds good..

There might be a way for you to Donate to the running costs of this site.. not sure.. best to PM a Mod or Admin to find out how..