title: Macro to find xlsm workbooks in given folders/sub folders based on criteria, then check a value in each workbook
body: <p>I'm trying to create a macro that lists all the files in a given folder (and its sub folders) which match criteria for filename (in my example "<em>job checklist</em>") and type (in my example "*.xlsm").  since all the workbooks of this type and naming convention in my search folder are of the same type, i need to open and read values from each workbook and copy them into my host workbook.  when the macro is run the run date/time should be noted in the host workbook, so that when the macro is run subsequently only new workbooks OR workbooks which have been modified since the most recent time stamp need to be opened and updated in the host workbook.</p>
  
  <p>I have been trying to use some recursive code found in other posts, but threads, but i'm having a hard time to incorporate search criteria:
  - file name
  - file type
  - modified date
  [here] (<a href="https://stackoverflow.com/questions/20687810/vba-macro-that-search-for-file-in-multiple-subfolders?newreg=d5bea4c6c336460da1c1ac1d028c20a7">VBA macro that search for file in multiple subfolders</a>)</p>
  
  <p>I have also tried to encorporate code from Pearson <a href="http://www.cpearson.com/excel/docprop.aspx" rel="nofollow noreferrer">here</a> to allow me to check file attributes of xls files but it doesnt seem to work (maybe due to 64 bit, though i found another version which was supposed to be compatible)</p>
  
  <p>I've been trying to find a solution for several days, but am kinda stuck, any help would be appreciated.</p>
  
  <p>working code i have so far which is listing all the files here of type .zip in my host workbook, i don't know how to check the modified date of a file.  i assume that if i could i could add some code to open files (which meet type, name and modified (compared to a date/time value cell in the host workbook, and updates every time the macro is run) and then extract values from a known sheet/range into the host workbook.</p>
  
  <pre><code>```vba
  Sub MainList()
      Dim folder, xdir As Variant
      Set folder = Application.FileDialog(msoFileDialogFolderPicker)
      Call ListFilesInFolder("C:\Users\60066690\Desktop\Documents from BCP and loose MTC", True)
  End Sub
  
  Sub ListFilesInFolder(ByVal xFolderName As String, ByVal xIsSubfolders As Boolean)
      Dim xFileSystemObject As Object
      Dim xFolder As Object
      Dim xSubFolder As Object
      Dim xFile As Object
      Dim rowIndex As Long
      Set xFileSystemObject = CreateObject("Scripting.FileSystemObject")
      Set xFolder = xFileSystemObject.GetFolder(xFolderName)
      rowIndex = Application.ActiveSheet.Range("A65536").End(xlUp).Row + 1
      For Each xFile In xFolder.Files
          If Not InStr(1, xFile.Name, ".zip") = 0 Then
  'could need to add in here If for name, but not sure how to add If for modified date, if i could i could compare the modified date to a date cell in this workbook
              Application.ActiveSheet.Cells(rowIndex, 1).Formula = xFile.Name
  'need to add some code in here to open the found workbook, then extract some values from known sheets/cells, store those values in variables, then close the found workbook and output the found variables to the colums beside the file name            
  rowIndex = rowIndex + 1
          End If
      Next xFile
      If xIsSubfolders Then
          For Each xSubFolder In xFolder.SubFolders
              ListFilesInFolder xSubFolder.Path, True
          Next xSubFolder
      End If
      Set xFile = Nothing
      Set xFolder = Nothing
      Set xFileSystemObject = Nothing
  End Sub
  ```
  </code></pre>