Copy File Names in Folder starting from 001_ to 010_

[howard]

I have tried to write code to copy file names in C:\Sales Reports stating from 001_ to 0010_ and pasting this in A1 onwards on sheet "File Names"

I only paste the file names stating from 001_ to 009_ and does not include 0010_


It would be appreciated if someone could amend my code

 Sub ExtractFileNames()
    Dim folderPath As String
    Dim fileName As String
    Dim ws As Worksheet
    Dim i As Integer
    Dim rowNum As Integer
    
    ' Set the folder path
    folderPath = "C:\Sales Reports\"
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("File names")
    
    ' Clear previous data in column A
    ws.Range("A1:A10").Clear
    
    ' Initialize row number
    rowNum = 1
    
    ' Loop through files and extract names
    For i = 1 To 10
        fileName = Dir(folderPath & Format(i, "000_") & "*.*")
        
        Do While fileName <> ""
            ws.Cells(rowNum, 1).Value = fileName
            rowNum = rowNum + 1
            fileName = Dir
        Loop
    Next i
End Sub

 

[howard]

I have managed to resolve the issue

 Sub ExtractFileNames()
    Dim folderPath As String
    Dim fileName As String
    Dim ws As Worksheet
    Dim i As Integer
    Dim rowNum As Integer
    Dim formatString As String
    
    ' Set the folder path
    folderPath = "C:\Sales Reports\"
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("File names")
    
    ' Clear previous data in column A
    ws.Range("A1:A10").Clear
    
    ' Initialize row number
    rowNum = 1
    
    ' Loop through files and extract names
    For i = 1 To 10
        If i = 10 Then
            formatString = Format(i, "000#_")
        Else
            formatString = Format(i, "00#_")
        End If
        
        fileName = Dir(folderPath & formatString & "*.*")
        
        Do While fileName <> ""
            ' Check if the file name matches the pattern
            If Left(fileName, Len(formatString)) = formatString Then
                ws.Cells(rowNum, 1).Value = fileName
                rowNum = rowNum + 1
            End If
            fileName = Dir
        Loop
    Next i
End Sub