Get filenames from a folder

[ThrottleWorks]

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.

'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]

You should be able to adapt this to your needs.

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]

I been using the following code for a long time:

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

 

[ThrottleWorks]

Hi @Hui sir, thanks a lot for the help.
It is working great ! Have a nice day ahead.

 

[ThrottleWorks]

Hi @AlanSidman Sir, thanks lot for the help.

This is very useful. Have a nice day ahead.

 

[Khalid NGO]

Good day Sachin,

I have found this useful too, we can get the list of files in a folder with options to rename:
https://strugglingtoexcel.com/2014/01/01/excel-batch-renamer/

 

[ThrottleWorks]

Hi @Khalid NGO thanks a lot for the help.
Have a nice day ahead.

 

[shrivallabha]

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.

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

 

[ThrottleWorks]

Hi @shrivallabha thanks a lot for the help.
Have a nice day ahead.