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 General
    Replies: 2
    Last Post: January 24th, 2008, 03:00
  2. Locate all XLS files on the C drive
    By Dovrox in forum Excel General
    Replies: 5
    Last Post: September 16th, 2004, 16:09
  3. Replies: 3
    Last Post: September 10th, 2004, 17: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