2007 Excel , No Application.FileSearch anymore Help

[myworld]

Hi,

Just got the new excel 2007 and foud it doesn't have the Application.FileSearch anymore, which we use quite a bit. I've been trying to work a way around it but have had no luck. I'm on a time crunch so thought for the first time I'll ask the internet for the first time out of desperation. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
What I need to do is use VBA to look into a folder and return the file names that are in the folder, in a column on an excel sheet and each of the names be a hyperlink to the file. The code I used before is below. I would greatly appreciate any help!!!<o></o>


Sub Allfiles()<o></o>
Dim myPath As String<o></o>
Dim lLen As Long, i As Long<o></o>
Dim parts<o></o>
<o> </o>
myPath = ThisWorkbook.Path & "\Files"
With Application.FileSearch<o></o>
.NewSearch<o></o>
.LookIn = myPath<o></o>
.SearchSubFolders = True<o></o>
.Filename = "*.*"<o></o>
Range("j2:k6000").Select<o></o>
Selection.ClearContents<o></o>
If .Execute() > 0 Then<o></o>
For i = 2 To .FoundFiles.Count<o></o>
parts = <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:City w:st="on"><st1lace w:st="on">Split</st1lace></st1:City>(Trim(Replace(Dir(.FoundFiles(i)), ".xls", "")))<o></o>
Cells(i, 8) = parts(UBound(parts))<o></o>
Cells(i, 9).FormulaR1C1 = "=Hyperlink(" & Chr(34) & .FoundFiles(i) _<o></o>
& Chr(34) & ",R[0]C[-1])"
<o> </o>
Else<o></o>
Range("a1").Select<o></o>
MsgBox "There were no files found."<o></o>
End If<o></o>
End With<o></o>
End Sub

 

[Richard Schollar]

Hi

There are multiple examples on the Board of filesearch alternatives. Several can be found within this thread:

http://www.mrexcel.com/forum/showthread.php?t=268046

 

[somnath_it2006]

After adding "Microsoft scripting Runtime" reference you can use below recursive function code for find files:

Dim xFile As File
Dim ObjFolder As Folder
Dim i As Long
Dim ws As Worksheet

Sub Allfiles()

    
    Set Fsys = CreateObject("Scripting.FileSystemObject")
    Set ws = ActiveSheet
    
    ' Static path
    Set ObjFolder = Fsys.GetFolder(ThisWorkbook.Path & "\Files" )
    
    i = 2
    
    Call FindFolder(ObjFolder)
        
    
    
End Sub
    
Function FindFolder(FolderPath As Folder) As Boolean
    
    Dim RootFolder As Folder
    ' Check for subfolders
    For Each RootFolder In FolderPath.SubFolders
        
        Call FindFiles(RootFolder)
        
        Call FindFolder(RootFolder)
            
    Next
    
End Function

Function FindFiles(xFolder As Folder)
    
    For Each xFile In xFolder.Files
            
        ws.Hyperlinks.Add Anchor:=ws.Range("I" & i), Address:=xFile.Path, TextToDisplay:=xFile.Name
        i = i + 1
        
    Next
    
End Function

 

[myworld]

Thank You Very Much Somnath, I turned on the "Microsoft scripting Runtime" tools>reference then I put the code in and triggered it, first it error'ed cause I changed the folder name so I fixed that. Then I triggered it again (i added it to a button) and it just blinked? Not sure why it did not doing anything else? Though I do thank you greatly for going to the bother of writting this up in the first place

 

[somnath_it2006]

Oh yes... I think you don't have any sub folder in "Files" folder and all files exist in "Files" folder only...

Just try below updated code:

Dim xFile As File
Dim ObjFolder As Folder
Dim i As Long
Dim ws As Worksheet

Sub Allfiles()

    
    Set Fsys = CreateObject("Scripting.FileSystemObject")
    Set ws = ActiveSheet
    
    ' Static path
    Set ObjFolder = Fsys.GetFolder(ThisWorkbook.Path & "\Files" )
    
    i = 2
    
    Call FindFiles(ObjFolder)
    Call FindFolder(ObjFolder)
    
    
    
End Sub
    
Function FindFolder(FolderPath As Folder) As Boolean
    
    Dim RootFolder As Folder
    ' Check for subfolders
    For Each RootFolder In FolderPath.SubFolders
        
        Call FindFiles(RootFolder)
        
        Call FindFolder(RootFolder)
            
    Next
    
End Function

Function FindFiles(xFolder As Folder)
    
    For Each xFile In xFolder.Files
            
        ws.Hyperlinks.Add Anchor:=ws.Range("I" & i), Address:=xFile.Path, TextToDisplay:=xFile.Name
        i = i + 1
        
    Next
    
End Function

Let me know how it works...

 

[myworld]

You are a master!!!! Thanks you very much for doing this! This means heaps! Again thank you very very much Worked like a dream!!! Thanks

 

[somnath_it2006]

You are most welcome............