VBA questions about listing in one cell

Try this:

 

1. Write in one cell the names of many Excel files in a folder:

Sub ListFilesInFolder()
    Dim folderPath As String
    Dim fileName As String
    Dim cell As Range

    folderPath = "C:\YourFolderPath\" ' Change to your folder path
    Set cell = ThisWorkbook.Sheets("Sheet1").Range("A1") ' Change to your desired cell

    fileName = Dir(folderPath & "*.xls*")
    Do While fileName <> ""
        cell.Value = cell.Value & fileName & "; "
        fileName = Dir
    Loop
End Sub

2. Arrange and list the names in the cell:

If you want the file names to be listed in a more readable format, you can use a line break (you can also modify the separator in the above code):

Sub ListFilesInFolder()
    Dim folderPath As String
    Dim fileName As String
    Dim cell As Range

    folderPath = "C:\YourFolderPath\" ' Change to your folder path
    Set cell = ThisWorkbook.Sheets("Sheet1").Range("A1") ' Change to your desired cell

    fileName = Dir(folderPath & "*.xls*")
    Do While fileName <> ""
        cell.Value = cell.Value & fileName & vbCrLf
        fileName = Dir
    Loop
End Sub

3. Repeat the task in a different cell but for different folder files:

You can create a general subroutine that takes the folder path and the cell as parameters to make it reusable:

Sub ListFilesInFolder(folderPath As String, targetCell As Range)
    Dim fileName As String

    fileName = Dir(folderPath & "*.xls*")
    Do While fileName <> ""
        targetCell.Value = targetCell.Value & fileName & vbCrLf
        fileName = Dir
    Loop
End Sub

Sub ExampleUsage()
    ListFilesInFolder "C:\FirstFolderPath\", ThisWorkbook.Sheets("Sheet1").Range("A1")
    ListFilesInFolder "C:\SecondFolderPath\", ThisWorkbook.Sheets("Sheet1").Range("B1")
End Sub

ListFilesInFolder is a general subroutine that lists the file names in the specified folder into the specified cell. ExampleUsage demonstrates how you can call this subroutine for different folders and cells.