Hi Kmac224,
Hope this helps
Option Explicit
Sub GetFilesInFolder()
Dim strFolder As String 'folder to be searched eg: c:\main
Dim lngFindHowManyFiles As Long 'no of files to be returned
strFolder = "c:\main"
lngFindHowManyFiles = 5
FilesFoundNewFolder strFolder, lngFindHowManyFiles
End Sub
Public Function FilesFoundNewFolder(strPath As String, lngNoFiles As Long)
Dim vaArray As Variant
Dim i As Integer
Dim ws As Excel.Worksheet
Dim oFile As Object
Dim oFSO As Object
Dim oFolder As Object
Dim oFiles As Object
Dim lngFilesFound As Long
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(strPath)
Set oFiles = oFolder.Files
If oFiles.Count = 0 Then Exit Function 'exit function no files found
ReDim vaArray(1 To oFiles.Count)
Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
If oFiles.Count > lngNoFiles Then
ws.Name = "First " & lngNoFiles & " Found"
Else
ws.Name = "Only " & oFiles.Count & " Found"
End If
ws.Range("A1").Value = "No"
ws.Range("B1").Value = "File Name"
ws.Range("C1").Value = "Found in Folder"
ws.Range("D1").Value = strPath
i = 1
For Each oFile In oFiles
ws.Range("A" & (i + 1)).Value = i
ws.Range("B" & (i + 1)).Value = oFile.Name
i = i + 1
If i > lngNoFiles Then Exit For 'only return required no of files
Next
ws.Cells.Columns.AutoFit
Erase vaArray
Set ws = Nothing
Set oFiles = Nothing
Set oFolder = Nothing
Set oFile = Nothing
Set oFSO = Nothing
End Function
Regards
