Application.FileSearch

[patrick_nyr]

Hi All,

I see that Application.FileSearch has been deprecated in Office 2007 and
that's
a bummer I really liked it's functionality. I had written a Visual Basic
macro to populate multiple worksheets in an Excel file from data I has
created from an SQR (Hyperion Software).

Can someone tell me the alternative for the following syntax?

If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderDescending) > 0 Then"

Thanks for your help.

 

[Don Guillett]

DIR

 

[Martin Brown]

DIR

But be aware that in some highly populated directories with entries that
span beyond some magic power of two and similar names ending in numbers
DIR will strangely stop returning answers before it runs out of entries.
I have seen it happen once at 64 but more usually at 256 or 1024. I
can't be sure of the exact conditions to trigger it so I am just
describing the situations where I have seen it happen.

Once it has happened new files added to the directory stay invisible to
XL2007. You have to delete some visible ones to make room for new files.
And then it is only the files added after some visible ones have been
zapped that can be seen. It isn't entirely reproducible but it is very
clear in the debugger that DIR() inside XL2007 tells lies.

I have observed this several times with XL2007 on Vista boxes.

MSKB gives an official workaround using FileSystemObject that is as
clunky as hell. YMMV

http://support.microsoft.com/kb/920229/en-us

Has a link to the official "fix" for this bodgeware.

When you ask about it the XL team blame Vista and Vista team blame XL
for Application.FileSearch woes. Given the huge number of other massive
blunders in XL2007 I know where I would put my money for cocking this
one up so monumentally.

Regards,
Martin Brown

 

[Peter T]

I've read reports that DIR can be problematic but I can't imagine why XL2007
should be related. Personally I've never had a problem with Dir but I always
reset first.


Function FilesToCol(sPath As String, sLike As String, c As Collection) As
Long
Dim sFile As String

If c Is Nothing Then Set c = New Collection
Call Dir("nul")
sFile = Dir(sPath & sLike)

Do While Len(sFile)
c.Add sFile
sFile = Dir()
Loop

FilesToCol = c.Count

End Function

Sub Test()
Dim cnt As Long, i As Long
Dim sPath As String, sFilesLike As String
Dim col As Collection

sPath = Application.DefaultFilePath
If Right$(sPath, 1) <> "\" Then sPath = sPath & "\"

sFilesLike = "*.xl*"
cnt = FilesToCol(sPath, sFilesLike, col)
Debug.Print cnt & " Excel files"

Range("a:a").Clear

If cnt Then
ReDim arr(1 To cnt, 1 To 1)

For i = 1 To cnt
arr(i, 1) = col(i)
Cells(i, 1) = col(i)
Next
Range("a1").Resize(cnt) = arr
End If

End Sub

Regards,
Peter T