Public Sub Data_Validation_PDF_Files2()
Dim mainFolder As String
Dim destCell As Range
Dim numRows As Long
mainFolder = "C:\Users\A-LL\Desktop\RET\"
Application.EnableEvents = False
With ThisWorkbook.Worksheets("Sheet1")
.Cells.Clear
Set destCell = .Range("B2")
numRows = List_PDF_Files(mainFolder, destCell)
With destCell.Resize(numRows)
.Validation.Delete
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & .Address
End With
End With
Application.EnableEvents = True
End Sub
Private Function List_PDF_Files(folderPath As String, destCell As Range) As Long
Static FSO As FileSystemObject
Dim FSfolder As Object, FSsubfolder As Object
Dim FSfile As Object
If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
'List files in this folder
List_PDF_Files = 0
Set FSfolder = FSO.GetFolder(folderPath)
For Each FSfile In FSfolder.Files
If LCase(FSfile.Name) Like "*.pdf" Then
destCell.Offset(List_PDF_Files).Value = FSfile.Name
List_PDF_Files = List_PDF_Files + 1
End If
Next
'List files in subfolders of this folder
For Each FSsubfolder In FSfolder.SubFolders
List_PDF_Files = List_PDF_Files + List_PDF_Files(FSsubfolder.Path, destCell.Offset(List_PDF_Files))
Next
End Function
Public Function Find_File(folderPath As String, findFileName As String) As String
Static FSO As FileSystemObject
Dim FSfolder As Object, FSsubfolder As Object
Dim FSfile As Object
If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
'Find the file in this folder
Find_File = ""
Set FSfolder = FSO.GetFolder(folderPath)
For Each FSfile In FSfolder.Files
If FSfile.Name = findFileName Then
Find_File = FSfile.Path
Exit For
End If
Next
If Find_File = "" Then
'Find file in subfolders of this folder
For Each FSsubfolder In FSfolder.SubFolders
Find_File = Find_File(FSsubfolder.Path, findFileName)
If Find_File <> "" Then
Exit For
End If
Next
End If
End Function
Public Function HasValidation(cell As Range) As Boolean
Dim t: t = Null
On Error Resume Next
t = cell.Validation.Type
On Error GoTo 0
HasValidation = Not IsNull(t)
End Function
