Application.FileSearch in 2007

[Amery]

I have a batch of code that works for any computer with 2003 or earlier
versions, and I have been told by 2007 that it no longer is allowed. Here's
the code..

dim fs as variant
Set fs = Application.FileSearch
With fs
.LookIn = Sheet2.Cells(6, 4)
.Filename = "*.xls"
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending)

0 Then

end if
End With

The code breaks at the set command and says something regarding the fact
that 2007 no longer supports the method or object. Does anyone know of a way
to get this to work in Excel '07?

 

[Jon Peltier]

You could use the FileSystemObject:

http://support.microsoft.com/kb/185601
http://www.vb-helper.com/howto_list_directory_hierarchy.html
http://www.vbaexpress.com/kb/getarticle.php?kb_id=232

for more:
http://www.google.com/search?ie=UTF-8&oe=UTF-8&q=vba+directory+search+fso

- Jon

 

[ilia]

It's a little clumsier with the Dir function. This function will
return a 2D variant array of file names listed in ascending sorted
order, in a directory. Sub Test() demonstrates usage:

Public Sub test()
Dim v As Variant

v = getDir(Sheet2.Cells(6, 4))
' print the last file name in the list
Debug.Print v(UBound(v), 1)
End Sub


Public Function getDir(path As String) As Variant
Dim fList() As String
Dim iPosition As Long
Dim iSize As Long
Dim sFile As String
Dim fRange As Excel.Range
Const iIncrement As Long = 50

iSize = iIncrement
ReDim fList(1 To iSize)

sFile = Dir(path & _
IIf(Right(path, 1) = "\", "", "\") & _
"*.xls")

Do While Len(sFile)
iPosition = iPosition + 1
If iPosition > iSize Then
iSize = iSize + iIncrement
ReDim Preserve fList(1 To iSize)
End If
fList(iPosition) = sFile
sFile = Dir
Loop

If iSize > iPosition Then
ReDim Preserve fList(1 To iPosition)
End If

Set fRange = Sheet2.Range("E1").Resize(iPosition, 1)
fRange.Value = WorksheetFunction.Transpose(fList)

fRange.Sort key1:=fRange.Cells(1), order1:=xlAscending
getDir = fRange.Value
fRange.Clear
End Function