Find and save the name of files in a folder using VBA excel.

Objective

To find and save the name of files in a folder using VBA excel.

Approach

Here we have invoked the command prompt and used Dir command in VBA code, using this approach we can have list of specific type of files or all type of files

Steps

1.Declare the path of the folder where files are located
2.Check if folder exists or not
3.If folder exists, then find the names of all the files using Dir command
4.CD command is used to change the directory to your defined folder, because you may be in some other directory while you are running this code.
5.Save the output [which is name of all the files ] of Dir command in output file MyFileNameList.txt
6.If folder does not exist then display the message that folder does not exist

Code

Sub ListFileNames()
Dim MyFolderPath As String
'Path where the folder is located, change the path as per your requirement
MyFolderPath = "C:\Users\YourUserName\Desktop\MyFolder"
'Check if the folder exists
If Dir(MyFolderPath, vbDirectory) <> "" Then
'It will save the names of all .xlsx in the folder in MyFileNameList.txt at C:\Users\ YourUserName \Desktop\MyFolderName
Call Shell("cmd.exe /c " & "cd " & MyFolderPath & " && dir /B /a-d  *.* > MyFileNameList.txt")
'Replace xlsx with *.* for any type of file or folder
'Display a message box if folder does not exist
Else
MsgBox "Folder does not exist"
End If
End Sub

We have tested the above mentioned code in below shown folder structure.Once the code code was executed the output was sent to the text file.In the image shown below, we can see that names of all the 4 files which were in the folder [i.e. MyFolder] , have been written to output file MyFileNameList.txt.

Notes

1. Cross check the path where your folder exists, it should be correct
2. Change the folder path in the code
3. Replace *.xlsx in the code with *.* for any type of file

Reference

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/shell-function

Post you may like

Create a folder by mkdir statement in VBA