populating list of PDF files from folders & subfolders within directory in dropdown and open it

[Alaa mg]

Hi
I have PDF files within folders & subfolders in this directory "C:\Users\A-LL\Desktop\RET"
the directory will be PDF files like this
"C:\Users\A-LL\Desktop\RET\A200.PDF"
"C:\Users\A-LL\Desktop\RET\INVNO\INB122.PDF"
"C:\Users\A-LL\Desktop\RET\INVNO\SSS\AW300.PDF"
so I would populate list of PDF files are existed in directory in column B when select B1,B2,B3.... and when select item from dropdown(data validation) then should open the file .

thanks

 

[John_w]

You want the PDF files to be listed in column B, starting at B1. However, it's not clear which cell is the data validation cell, so I've assumed cell F1 in the code below.

Code in a standard module. This macro creates the list of PDFs in column B on Sheet1 and the data validation dropdown in F1.

Public Sub Data_Validation_PDF_Files()
     
    Dim mainFolder As String
    Dim destCell As Range
    Dim numRows As Long
     
    mainFolder = "C:\Users\A-LL\Desktop\RET\"
 
    With ThisWorkbook.Worksheets("Sheet1")
        .Cells.Clear
        Set destCell = .Range("B1")
        numRows = List_PDF_Files(mainFolder, destCell)
        With .Range("F1").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & destCell.Resize(numRows).Address
        End With
    End With
 
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
        destCell.Offset(List_PDF_Files).Value = FSfile.Path
        List_PDF_Files = List_PDF_Files + 1
    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

Code in the Sheet1 module. This Worksheet_Change event handler opens the selected PDF when you select a PDF in the F1 data validation dropdown.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$F$1" Then   'the Data Validation cell
        ThisWorkbook.FollowHyperlink Target.Value
    End If
End Sub

 

[Alaa mg]

thanks.
but when run the macro to create list I don't want showing directory for each file. just I want showing file name.
also will show two EXCEL messages when I try to open file . I would when select the file from dropdown should open directly.
as to this .

it's not clear which cell is the data validation cell, so I've assumed cell F1 in the code below.

I would when select any cell for column B then show show data validation for any selected cell in column B , not specific cell as you did it .

 

[John_w]

but when run the macro to create list I don't want showing directory for each file. just I want showing file name.

OK, the code below shows just the file names.

also will show two EXCEL messages when I try to open file .

Which messages are you referring to?

I would when select any cell for column B then show show data validation for any selected cell in column B , not specific cell as you did it .

Still not totally clear exactly what the data validation should be. If I understand you correctly, you want a data validation dropdown in every cell with a file name in column B, however it's not totally clear what the contents of each data validation should be; in the code below each data validation cell consists of just the file name in that cell.

Code in a standard module.

Public Sub Data_Validation_PDF_Files2()
       
    Dim mainFolder As String
    Dim destCell As Range
    Dim numRows As Long, i As Long
       
    mainFolder = "C:\Users\A-LL\Desktop\RET\"
       
    Application.EnableEvents = False
    With ThisWorkbook.Worksheets("Sheet1")
        .Cells.Clear
        Set destCell = .Range("B1")
        numRows = List_PDF_Files(mainFolder, destCell)
        For i = 0 To numRows - 1
            With destCell.Offset(i).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & destCell.Offset(i).Address
            End With
        Next
    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
        destCell.Offset(List_PDF_Files).Value = FSfile.Name
        List_PDF_Files = List_PDF_Files + 1
    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

Code in the Sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim mainFolder As String
    Dim fullFileName As String
       
    mainFolder = "C:\Users\A-LL\Desktop\RET\"
   
    If Target.Column = 2 And Target.Cells.Count = 1 Then
        If HasValidation(Target) Then
            fullFileName = Find_File(mainFolder, Target.Value)
            ThisWorkbook.FollowHyperlink fullFileName
        End If
    End If
   
End Sub

 

[Alaa mg]

thank for updating and it's perfect except the message.

Which messages are you referring to?

"The file and its viewer application are set to be launched by this PDF file. The file may contain programs, macros, or viruses that could potentially harm your computer. Only open the file if you are sure it is safe. If this file was placed by a trusted person or program, you can click Open to view the file."

I don't want the messages .
they are really disturbing.

 

[John_w]

Try this modified Worksheet_Change, which turns off Excel's alerts before opening the PDF.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim mainFolder As String
    Dim fullFileName As String
        
    mainFolder = "C:\Users\A-LL\Desktop\RET\"
    
    If Target.Column = 2 And Target.Cells.CountLarge = 1 Then
        If HasValidation(Target) Then
            fullFileName = Find_File(mainFolder, Target.Value)
            Application.DisplayAlerts = False
            On Error Resume Next  'in case user clicks Cancel on the warning message
            ThisWorkbook.FollowHyperlink fullFileName
            On Error GoTo 0
            Application.DisplayAlerts = True
        End If
    End If
    
End Sub

 

[John_w]

I've just tried my suggested fix and it didn't work - I still get this warning:

My only other suggestion is to read this article:

Enable or disable hyperlink warning messages in Office programs - Microsoft 365

Describes how to enable or to disable hyperlink warning messages in Microsoft Office programs.

learn.microsoft.com

 

[Alaa mg]

I've just tried my suggested fix and it didn't work - I still get this warning:

me too .

My only other suggestion is to read this article:

I will read it soon.

If I understand you correctly, you want a data validation dropdown in every cell with a file name in column B,

I'm afraid you misunderstood !
sho
should change this
Set destCell = .Range("B1")

to this
Set destCell = .Range("B:B") with exclude B1 should start from B2

each cell in column B should contain list for all of files
for instance :
I have
INN.PDF
SS.PDF
DATA.PDF
so when select B2
will populate list

INN.PDF
SS.PDF
DATA.PDF
and when select B3
will populate list
INN.PDF
SS.PDF
DATA.PDF
.
and so on for each cell in column B

 

[John_w]

should change this
Set destCell = .Range("B1")

to this
Set destCell = .Range("B:B") with exclude B1 should start from B2

each cell in column B should contain list for all of files

OK, this puts the file names in B2, B3, etc. And each cell of those cells has a data validation containing all the files.

This code replaces the previous Data_Validation_PDF_Files2 code in post #4. All the other code stays the same.

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("Sheet2")
        .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

 

[Alaa mg]

it shows the directory whole in list as in post#2 !
ans when try select item to open will show complie error in this word HasValidation for this line in sheet module

If HasValidation(Target) Then