0

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 "job checklist") 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.

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] (VBA macro that search for file in multiple subfolders)

I have also tried to encorporate code from Pearson here 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)

I've been trying to find a solution for several days, but am kinda stuck, any help would be appreciated.

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.

```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
```
Colin Bruce-Alleyne
  • 1
  • 2
  • It would be helpful to know where exactly you are stuck. Can you please [edit your question](https://stackoverflow.com/posts/55381425/edit) to include the code you have? A clear explanation of how it, specifically, does not meet expectations would also be useful – cybernetic.nomad Mar 27 '19 at 15:53
  • Try: https://www.thespreadsheetguru.com/the-code-vault/2014/4/23/loop-through-all-excel-files-in-a-given-folder and use For Each SubFolder In Folder.SubFolders to get in subfolders – W-hit Mar 27 '19 at 16:05
  • One the one hand you display only "zip" files with your code. On the other hand you state in your comments that you want to open workbook files? It's unclear what you are trying to achive! – simple-solution Mar 27 '19 at 16:52
  • see https://learn.microsoft.com/de-de/office/vba/language/reference/user-interface-help/file-object – simple-solution Mar 27 '19 at 16:56
  • add: Application.ActiveSheet.Cells(rowIndex, 2).Value = xFile.DateLastModified – simple-solution Mar 27 '19 at 16:57

0 Answers0