Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.FileSearch in 2007
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.FileSearch in 2007
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 mo 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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.FileSearch in 2007
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 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Application.FileSearch | Excel Discussion (Misc queries) | |||
replacement for Application.FileSearch in 2007 | Excel Programming | |||
What is better: Application.FileSearch or Dir ?? | Excel Programming | |||
Application.Filesearch | Excel Programming | |||
VBA Application.FileSearch | Excel Programming |