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?

You could use the FileSystemObject:

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

for more:
http://www.google.com/search?ie=UTF-...ory+search+fso

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Amery" <(E-Mail Removed)> wrote in message
news:CA7EA664-E487-4CA0-A1F2-(E-Mail Removed)...
>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?

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




On Dec 13, 4:23 pm, Amery <Am...@discussions.microsoft.com> wrote:
> 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?