No announcement yet.

vba Search hard drive files not list all the files

  • Filter
  • Time
  • Show
Clear All
new posts

  • vba Search hard drive files not list all the files

    Hi I have this macro to search my hard drive and list all the files in the hard drive including sub folders. But when I run it, it does list files in the sub folders, but the files in the "mother" folder does not appears on the search result. like under folder C:\myfile i have 4 files include one sub folder C:\myfile\testfolder, it will list everything in the testfolder but not the 4 files in the C:\myfile. Here is the code. where did I do wrong??

    Sub Directorylisting()
        Workbooks.Open ("C:\dltemp.xltm") ' create a new workbook for the file list
       Sheets("Raw Data").Select
        Range("A1").Formula = "File Name:"
        Range("B1").Formula = "Full File Name:"
        Range("C1").Formula = "File Path:"
        Range("D1").Formula = "File Type:"
        Range("E1").Formula = "Date Created:"
        Range("F1").Formula = "Date Last Accessed:"
        Range("G1").Formula = "Date Last Modified:"
        Range("H1").Formula = "Location"
        Range("A1:H1").Font.Bold = True
        ListFilesInFolder "C:\User\", True
        ' list all files included subfolders
    End Sub
    Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
    ' lists information about the files in SourceFolder
    ' example: ListFilesInFolder "C:\FolderName\", True
    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
    Dim FileItem As Scripting.File
    Dim r As Long
    Dim LR As Long
        Set FSO = New Scripting.FileSystemObject
        Set SourceFolder = FSO.GetFolder(SourceFolderName)
        r = Range("A65536").End(xlUp).Row + 1
        For Each FileItem In SourceFolder.Files
            ' display file properties
            Cells(r, 2).Formula = FileItem.Name
            Cells(r, 3).Formula = FileItem.Path
            Cells(r, 4).Formula = FileItem.Type
            Cells(r, 5).Formula = FileItem.DateCreated
            Cells(r, 6).Formula = FileItem.DateLastAccessed
            Cells(r, 7).Formula = FileItem.DateLastModified
            Cells(r, 8).Formula = FileItem.ParentFolder
            'Cells(r, 1).Formula = "=LEFT(B2,FIND(""."",B2&""."")-1)"
            r = r + 1 ' next row number
        Next FileItem
        If IncludeSubfolders Then
            For Each SubFolder In SourceFolder.SubFolders
                ListFilesInFolder SubFolder.Path, True
            Next SubFolder
        End If
    LR = Range("B" & Rows.Count).End(xlUp).Row
    Range("A2:A" & LR).Formula = _
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("A2:A" & LR).Copy
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("A2:A" & LR).PasteSpecial xlPasteValues
        Set FileItem = Nothing
        Set SourceFolder = Nothing
        Set FSO = Nothing
        ActiveWorkbook.Saved = True
    Range("A1").Formula = "Input Data Below"
    Range("B1").Formula = "Location"
    Range("D1").Formula = "Click to View and Save as"
    Range("A1:D1").Font.Bold = True
    Range("A1:D1").Font.Size = 13
    Sheets("Raw Data").Select
    End Sub
    This is the strip extension that the last bit of the code calls

    Public Function StripExtn(cell As Range)Dim RevStr As String
    Dim length As Integer
    RevStr = StrReverse(cell.Value)
    length = Len(RevStr) - InStr(1, RevStr, ".")
    StripExtn = StrReverse(Right(RevStr, length))
    End Function

  • #2
    Re: vba Search hard drive files not list all the files

    any good suggestions??


    • #3

      Re: vba Search hard drive files not list all the files

      Possibly because you put the file name in Col B but query Col A for the last used row - so overwriting the list every time it scans a new directory...?