VBA to List all Files in a Particular Folder

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

VBA Function for the Last Non-blank Row in a Worksheet

The VBA function below returns the number of the last row with a non-blank cell. It is a modified version of the functions found at www.ozgrid.com/VBA/ExcelRanges.htm, and includes VBA to unfilter the data before searching for the last non-blank row.

Function LastRowInWorksheet(ByRef wksheet As Worksheet) As Long
'-----------------------------------------------------------------------'
'   Returns the last row in the worksheet with a non-blank cell         '
'   http://www.ozgrid.com/VBA/ExcelRanges.htm                           '
'-----------------------------------------------------------------------'
    Dim lastRow As Long
   
    'Make all the data on the worksheet available
    On Error Resume Next
    wksheet.ShowAllData
    On Error GoTo 0
   
    'Check there are non-blank cells in the worksheet
    If Application.WorksheetFunction.CountA(wksheet.Cells) = 0 Then
        lastRow = 0
    Else
        lastRow = wksheet.Cells.Find(What:="*", After:=wksheet.Range("A1"), _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row
    End If

    'Assign a value to the function
    LastRowInWorksheet = lastRow
End Function