dj44
11-01-2016, 05:05 PM
Hi Folks,
Hope everyone is doing great.:)
I wanted to know if it was possible for me to filter and hyperlink to only specific files.
Example only all the pdfs and docs.
Exclude all the text files and excel files.
I couldn’t find anything to help me with the script below.
Public Sub ListFiles()
' found on 'http://www.ozgrid.com/forum/showthread.php?t=175318
Dim startFolderPath As String
Dim startCell As Range
startFolderPath = "C:\Users\DJLaptop\Desktop\Work"
Set startCell = Sheets("Files").Range("C9")
'startCell.Parent.Cells.Clear
List_Folders_and_Files startFolderPath, startCell
End Sub
Private Function List_Folders_and_Files(folderPath As String, destCell As Range) As Long
Static FSO As Object
Dim thisFolder As Object, subfolder As Object
Dim fileItem As Object
Dim n As Long
If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
Set thisFolder = FSO.GetFolder(folderPath)
'Add hyperlink for this folder
destCell.Parent.Hyperlinks.Add Anchor:=destCell, Address:=thisFolder.Path, TextToDisplay:=thisFolder.Name
'List subfolders and files in this folder
For Each subfolder In thisFolder.SubFolders
Set destCell = destCell.Offset(0, 1)
Set destCell = destCell.Offset(List_Folders_and_Files(subfolder.Path, destCell), -1)
Next
'Add hyperlink for each file in this folder
n = 0
For Each fileItem In thisFolder.Files
destCell.Offset(n, 1).Parent.Hyperlinks.Add Anchor:=destCell.Offset(n, 1), Address:=fileItem.Path, TextToDisplay:=fileItem.Name
n = n + 1
Next
List_Folders_and_Files = n
End Function
Please do advise where exactly I put the filter for my files to hyperlink to only pdf files and docx files
Hope everyone is doing great.:)
I wanted to know if it was possible for me to filter and hyperlink to only specific files.
Example only all the pdfs and docs.
Exclude all the text files and excel files.
I couldn’t find anything to help me with the script below.
Public Sub ListFiles()
' found on 'http://www.ozgrid.com/forum/showthread.php?t=175318
Dim startFolderPath As String
Dim startCell As Range
startFolderPath = "C:\Users\DJLaptop\Desktop\Work"
Set startCell = Sheets("Files").Range("C9")
'startCell.Parent.Cells.Clear
List_Folders_and_Files startFolderPath, startCell
End Sub
Private Function List_Folders_and_Files(folderPath As String, destCell As Range) As Long
Static FSO As Object
Dim thisFolder As Object, subfolder As Object
Dim fileItem As Object
Dim n As Long
If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")
Set thisFolder = FSO.GetFolder(folderPath)
'Add hyperlink for this folder
destCell.Parent.Hyperlinks.Add Anchor:=destCell, Address:=thisFolder.Path, TextToDisplay:=thisFolder.Name
'List subfolders and files in this folder
For Each subfolder In thisFolder.SubFolders
Set destCell = destCell.Offset(0, 1)
Set destCell = destCell.Offset(List_Folders_and_Files(subfolder.Path, destCell), -1)
Next
'Add hyperlink for each file in this folder
n = 0
For Each fileItem In thisFolder.Files
destCell.Offset(n, 1).Parent.Hyperlinks.Add Anchor:=destCell.Offset(n, 1), Address:=fileItem.Path, TextToDisplay:=fileItem.Name
n = n + 1
Next
List_Folders_and_Files = n
End Function
Please do advise where exactly I put the filter for my files to hyperlink to only pdf files and docx files