Here's how to code VBA for creating a current directory list:
1. Using Dir Function:
The Dir function allows iterating through files in a directory. Here's an example:
VBA
Sub ListCurrentDirFiles()
Dim fileName As String
Dim currentDir As String
' Get current directory path
currentDir = CurDir
' Loop through files using Dir function
fileName = Dir(currentDir & "\*.*") ' Replace "*.*" with specific filter if needed
' Loop until there are no more files
Do While fileName <> ""
' Display filename (add to worksheet cells, textbox, etc.)
Debug.Print fileName ' Replace with your desired output method (e.g., Worksheets("Sheet1").Range("A1").Value = fileName)
fileName = Dir ' Get next file name
Loop
End Sub
2. Using FileSystemObject:
The FileSystemObject offers more comprehensive file system manipulation. Here's an example:
VBA
Sub ListCurrentDirFiles_FSO()
Dim fso As Object
Dim folder As Object
Dim file As Object
' Create FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
' Get current directory as a folder object
Set folder = fso.GetFolder(CurDir)
' Loop through files in the folder
For Each file In folder.Files
' Display filename (add to worksheet cells, textbox, etc.)
Debug.Print file.Name ' Replace with your desired output method
Next file
End Sub
Explanation:
- Both methods retrieve the current directory path using
CurDir. - The first method uses
Dirto iterate through filenames with a wildcard filter (*.*for all files). - The second method creates a
FileSystemObjectand gets the current directory as a folder object. Then, it loops through each file object within the folder and retrieves its name.
Customization:
- Replace
Debug.Print fileNamewith your desired output method, such as adding filenames to a worksheet range, textbox in a UserForm, or building a list in a loop. - You can modify the code to filter files based on extensions (e.g.,
*.xlsxfor Excel files).
Choose the method that best suits your needs and coding style. Remember to replace the output statements (e.g., Debug.Print) with your specific implementation.
