Hyperlink to Files in Directory - Filter for Specific File Types ONLY to link to

[dj44]

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

[mancubus]

?


Sub vbax_57611_list_doc_pdf_files_create_hyperlinks()

Dim startFolderPath As String
Dim ArrFilesDoc, ArrFilesPdf
Dim i As Long

startFolderPath = "C:\Users\DJLaptop\Desktop\Work\"
ArrFilesDoc = Split(CreateObject("WScript.Shell").Exec("cmd /c Dir """ & startFolderPath & "*.doc*"" /b/s").StdOut.ReadAll, vbCrLf)
ArrFilesPdf = Split(CreateObject("WScript.Shell").Exec("cmd /c Dir """ & startFolderPath & "*.pdf*"" /b/s").StdOut.ReadAll, vbCrLf)

With Worksheets("Sheet1") 'Change Sheet1 to suit
.Cells(1).CurrentRegion.Offset(1).Clear
.Cells(2, 1).Resize(UBound(ArrFilesDoc)) = Application.Transpose(ArrFilesDoc)
.Cells(1 + UBound(ArrFilesDoc), 1).Resize(UBound(ArrFilesPdf)) = Application.Transpose(ArrFilesPdf)
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(i, 1).Hyperlinks.Add _
Anchor:=.Cells(i, 1), _
Address:=.Cells(i, 1), _
TextToDisplay:=CreateObject("Scripting.FileSystemObject").GetBaseName(.Cells(i, 1))
Next i
End With

End Sub



check this out too...

http://www.vbaexpress.com/forum/showthread.php?54127-Hyperlink-to-be-revised-help-please

[dj44]

Hello Mancubus,

Thank you for the code and showing me how to filter the docx.

I have briefly come across the wscript and cmd window but I'm a bit apprehensive about the black window when I tried it once - it looked as if it did something I didn’t understand, but you can use it in VBA? Well i'll investigate this.

Thank you for the additional link - I will read up on that lots of code there and make some changes to my code.

Have a great day :)

And folks too

[Kenneth Hobs]

Good example mancubus!

Shell commands are often very fast but sometimes need a shellexecutewait routine. If you are old enough to know DOS, then it will make sense. If you press Win+R and type cmd, you will be at a DOS prompt, err, command prompt. Type, Help Dir, and press enter key to get help. This site shows help for the command shell's Dir command. Do not confuse it with the VBA command Dir.

'Command line switches for the shell's Dir, http://ss64.com/nt/dir.html

Change the /c to /k to Keep the command shell window open. I like to test shell commands manually to see the results before scripting. Press the up arrow key to recall an issued shell command in that dialog.

If you are the one using that code, the hard coded path is fine. If not, that can be coded for any user though it would need to add the Work subfolder.

Of course we could make similar fso routines but shell code is usually the most efficient and concise method.

[dj44]

Hi Mancubus,

Thank you again for the inspiration about the cmd window.
I have looked at it further and have learned how to open one.

Although it took me ages to type things in there - you can paste too so that helped.


I can also filter now for only text files so I don’t have a quadzillion mp3 files annd hyeprlinking to all sorts I had in my folders that shouldnt have been there in the first place :grinhalo:



Thanks again folks , great piece of code