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