Forum Discussion
NoufN
Feb 11, 2025Copper Contributor
VBA questions about listing in one cell
Hello, I am new in using VBA in Excel. I have three related questions: - How can I write in one cell in excel file the names for many excel files at one folder? - How can I arrange and list the ...
Kidd_Ip
Feb 12, 2025MVP
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.