Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.FileSearch
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.FileSearch
DIR
-- Don Guillett Microsoft MVP Excel SalesAid Software "patrick_nyr" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.FileSearch
Don Guillett wrote:
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.FileSearch
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 "Martin Brown" wrote in message ... Don Guillett wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
with application.filesearch | Excel Programming | |||
Set fs = Application.FileSearch | Excel Programming | |||
Application.FileSearch Help | Excel Programming | |||
Application.Filesearch | Excel Programming | |||
VBA Application.FileSearch | Excel Programming |