January 26, 2013

search subfolders and Add hyperlink for file from the top level folder in Excel

To search subfolders and Add hyperlink for file from the top level folder using Macro code in Excel. You can take help from code below.

Private Files As Dictionary
Private StrFile As String
Public Function FindFolder(strpath As String) As String
Dim fs, f1, f2, FleCollection
Set fs = CreateObject("scripting.filesystemobject&quot ;)
Set f1 = fs.getfolder(strpath)
Set f2 = f1.subfolders
Set FleCollection = f1.Files
For Each subfld In f2
FindFolder subfld.Path
Next
For Each fle In FleCollection
Files.Add fle.Name, fle.Path
Next
End Function


'add below code before adding hyperlink

If (Files Is Nothing) Then
Set Files = New Dictionary
End If
'Filename should contain Extension like "test.pdf"
If Not ((Files.Exists(Filename))) Then
'replace c:\ with root folder of the file,if you are no sure about  Root folder, You need to call it with all possible drives like "C:\" "D:\" etc
FindFolder "c:\"
Else
Filename = Files.Item(Filename)
End If
 

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.