application.filesearch problem

[BBird]

I am currently trying to get a solution for the application.filesearch code that is no longer present in Excel 2007. I have done some searching and not sure if using the Dir method will work for my situation. Basically all I am trying to do is search a specific folder for the .txt files in it and hyperlink link them as a way for the user to have a quick check list to be sure they are checking all the files. Can someone please give me a hand? Thanks.

Sub getfilename()

Sheets("FileChooser").Select
Range("B1:B1000").Select
Selection.ClearContents

Dim i As Long
With Application.FileSearch
.NewSearch
.LookIn = "O:\americolddatafiles\receivedfiles\UnProcessed\"
.Filename = "*.txt"
.SearchSubFolders = False
.MatchTextExactly = False
.TextOrProperty = "ISA*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Sheets("FileChooser").Select
Range("B" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Hyperlinks.Add anchor:=Selection, Address:=.FoundFiles(i)
Next i
End If
End With
End Sub

 

[Datsmart]

Excel 2007 does not support FileSearch, but it does support FSO, (FileSystemObject).
http://www.techbookreport.com/tutorials/fso2.html

The above link should help you.

 

[Domenic]

Maybe...

[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] GetFilenames()

    [color=darkblue]Dim[/color] wksDest [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] rngCell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] Cnt [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]Set[/color] wksDest = Sheets("FileChooser")
    
    wksDest.Columns("B").ClearContents
    
    strPath = "O:\americolddatafiles\receivedfiles\UnProcessed\"
    
    [color=darkblue]If[/color] Right(strPath, 1) <> "\" [color=darkblue]Then[/color] strPath = strPath & "\"
    
    strFile = Dir(strPath & "*.txt")
    
    [color=darkblue]Do[/color] [color=darkblue]While[/color] Len(strFile) > 0
        Cnt = Cnt + 1
        [color=darkblue]With[/color] wksDest
            [color=darkblue]Set[/color] rngCell = .Cells(.Rows.Count, "B").End(xlUp)(2)
            .Hyperlinks.Add Anchor:=rngCell, Address:=strPath & strFile
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        strFile = Dir
    [color=darkblue]Loop[/color]
        
    [color=darkblue]If[/color] Cnt = 0 Then _
        MsgBox "No files found...", vbExclamation
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color][/font]

 

[BBird]

Thanks Domenic. I tried your code and it appears to do what I need. Thanks for the help.

 

[Domenic]

You're very welcome! Thanks for your feedback!