• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get filenames from a folder

ThrottleWorks

Excel Ninja
Hi,

I am using below mentioned code to get filenames from a folder.
However, I am facing a bug at ‘With Application.FileSearch’ line.

The code is copied from ‘https://www.automateexcel.com/vba/list-all-files/’.
Can anyone please help me in this.

Code:
'https://www.automateexcel.com/vba/list-all-files/
Sub List_All_The_Files_Within_Path()
    Dim Row_No As Integer
    Dim No_Of_Files As Integer
    Dim kk25 As Integer
    Dim File_Path As String
  
    File_Path = "C:\Users\a\Desktop\b"
  
    Row_No = 1
  
    'Lists all the files in the current directory
  
    With Application.FileSearch
        .NewSearch
        .LookIn = File_Path
        .Filename = "*.*"
        .SearchSubFolders = False
        .Execute
      
        No_Of_Files = .FoundFiles.Count
      
        For kk25 = 1 To No_Of_Files
            Worksheets("Sheet1").Cells(kk25 + 5, 15).Value = .FoundFiles(kk25)
        Next kk25
    End With
End Sub
 

AlanSidman

Active Member
You should be able to adapt this to your needs.

Code:
Sub ListAllFile()

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim ws As Worksheet
    Dim sPath As String
    Dim lrA As Long
    Dim lrB As Long

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set ws = Worksheets.Add

    'Get the folder object associated with the directory
    sPath = InputBox("What is the full Path to Search?")
    Set objFolder = objFSO.GetFolder(sPath)
    ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & " are:"
    ws.Cells(1, 2).Value = "The files found have modified dates:"
    ws.Cells(1, 3).Value = "The file Size is:"

    'Loop through the Files collection
    For Each objFile In objFolder.Files
    'If objFile.Name Like "*.pdf" Then
        lrA = Range("A" & Rows.Count).End(xlUp).Row
        lrB = Range("B" & Rows.Count).End(xlUp).Row
        ws.Range("A" & lrA + 1).Value = objFile.Name
        ws.Range("B" & lrB + 1).Value = objFile.DateLastModified
        ws.Range("C" & lrB + 1).Value = objFile.Size
    'End If
    Next
    'ws.Cells(2, 1).Delete
    'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing

End Sub
 

Hui

Excel Ninja
Staff member
I been using the following code for a long time:

Code:
Sub List_All_The_Files_Within_Path()
  Dim kk25 As Integer
  Dim myWs As Worksheet
  Dim File_Path As String
  Dim fileNamesCol As New Collection
  Dim MyFile As Variant
 
  File_Path = "C:\Users\a\Desktop\b\"  'Change to suit
 
  Row_No = 1
 
  'Lists all the files in the current directory
  MyFile = Dir$(File_Path & "*.xlsx")
  Do While MyFile <> ""
      fileNamesCol.Add (Replace(MyFile, ".xlsx", ""))
      MyFile = Dir$
  Loop

  'Dump collection to Worksheet
  Set myWs = Sheets("Sheet1")
  kk25 = 1
  For Each MyFile In fileNamesCol
      myWs.Range("O" & 5 + kk25).Value = fileNamesCol(kk25)
      kk25 = kk25 + 1
  Next MyFile

End Sub
 

shrivallabha

Excel Ninja
Hi,

I am using below mentioned code to get filenames from a folder.
However, I am facing a bug at ‘With Application.FileSearch’ line.

The code is copied from ‘https://www.automateexcel.com/vba/list-all-files/’.
Can anyone please help me in this.

Code:
'https://www.automateexcel.com/vba/list-all-files/
Sub List_All_The_Files_Within_Path()
    Dim Row_No As Integer
    Dim No_Of_Files As Integer
    Dim kk25 As Integer
    Dim File_Path As String
 
    File_Path = "C:\Users\a\Desktop\b"
 
    Row_No = 1
 
    'Lists all the files in the current directory
 
    With Application.FileSearch
        .NewSearch
        .LookIn = File_Path
        .Filename = "*.*"
        .SearchSubFolders = False
        .Execute
     
        No_Of_Files = .FoundFiles.Count
     
        For kk25 = 1 To No_Of_Files
            Worksheets("Sheet1").Cells(kk25 + 5, 15).Value = .FoundFiles(kk25)
        Next kk25
    End With
End Sub
For your information, Application.FileSearch was deprecated long back. It will work in Excel 2003 and lower versions.
 
Top