Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 3 of 3

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

  1. #1
    Join Date
    6th April 2012

    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

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    6th April 2012

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

    any good suggestions??

    Excel Video Tutorials / Excel Dashboards Reports

  3. #3
    Join Date
    1st September 2010

    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...?

    Excel Video Tutorials / Excel Dashboards Reports

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Search CSV Files For Word & Make .xls Files Of Them
    By ncap0mfq in forum EXCEL HELP
    Replies: 2
    Last Post: January 24th, 2008, 03:00
  2. Locate all XLS files on the C drive
    By Dovrox in forum EXCEL HELP
    Replies: 5
    Last Post: September 16th, 2004, 17:09
  3. Replies: 3
    Last Post: September 10th, 2004, 18:41


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts