Suppose you want to list all the files in a particular root folder, including any files in any subfolders. The VBA for doing so can be found at http://easyprograming.com/index.php/vb-script/34-list-all-files-and-subfolders-inside-a-folder-using-vbscript. Below I have taken this VBA and added an input box into which the user enters the path of the root folder e.g. C:\Documents. A CSV file called OutputFiles.csv is created to store the list of files. The location of OutputFiles.csv can be changed by editing the string “C:\OutputFiles.csv”
Option Explicit Option Base 1 Public fso Public ObjOutFile Sub GetUserInput() Dim rootFolderPath As String 'Get the user to enter the root folder rootFolderPath = InputBox(Prompt:="Enter root folder path: ", Title:="Enter root folder path") 'Check the input is not null If rootFolderPath = vbNullString Then MsgBox "No folder address entered", vbCritical Exit Sub End If 'Check the folder exists If Len(Dir(rootFolderPath, vbDirectory)) = 0 Then MsgBox "The folder " & vbCrLf & rootFolderPath & vbCrLf & " does not exist", vbCritical Exit Sub End If 'Add a slash at the end of the root folder path if not present If Right(rootFolderPath, 1) <> "\" Then rootFolderPath = rootFolderPath & "\" End If 'Create the File System Object Set fso = CreateObject("Scripting.FileSystemObject") 'Create the output file Set ObjOutFile = fso.CreateTextFile("C:\OutputFiles.csv") 'Write the CSV headers ObjOutFile.WriteLine ("Type,File Name,File Path") 'Call the GetFiles function to get all files GetFiles (rootFolderPath) 'Close the output file ObjOutFile.Close 'Display completed message MsgBox "Macro completed", vbInformation End Sub Function GetFiles(ByVal FolderName As String) 'http://easyprograming.com/index.php/vb-script/34-list-all-files-and-subfolders-inside-a-folder-using-vbscript On Error Resume Next Dim ObjFolder Dim ObjSubFolders Dim ObjSubFolder Dim ObjFiles Dim ObjFile Set ObjFolder = fso.GetFolder(FolderName) Set ObjFiles = ObjFolder.Files 'Write all files in this folder to output file For Each ObjFile In ObjFiles ObjOutFile.WriteLine ("File," & ObjFile.Name & "," & ObjFile.Path) Next 'Get all subfolders in this folder Set ObjSubFolders = ObjFolder.SubFolders For Each ObjFolder In ObjSubFolders 'Write subfolder name and path ObjOutFile.WriteLine ("Folder," & ObjFolder.Name & "," & ObjFolder.Path) 'Call the GetFiles function to get all files GetFiles (ObjFolder.Path) Next End Function