Excel 2007 filesearch

[Libby]

Hi all,

I have a list box that is populated by code to show the contents of a folder.
I use the Application.Filesearch to populate the listbox with the name of
the files in the folder.

This worked fine in Excel 2003, but for some reason Excel 2007 spits it out
with an error message (can't remember what it is).

Basically, I need to populate a listbox with the files in a folder.

All help appreciated.

Libby x

 

[Jim Cone]

Actually Filesearch did not work all that well. It was removed from XL 2007.
You can use the "Dir" function or the ScriptingRuntime "FileSystemObject".
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Libby"
wrote in message
Hi all,
I have a list box that is populated by code to show the contents of a folder.
I use the Application.Filesearch to populate the listbox with the name of
the files in the folder.
This worked fine in Excel 2003, but for some reason Excel 2007 spits it out
with an error message (can't remember what it is).

Basically, I need to populate a listbox with the files in a folder.
All help appreciated.
Libby x

 

[ilia]

There is no FileSearch in Excel 2007 (or other Office 2007 programs).

You can use Dir. Here's a function I use that returns an array of
files in a directory. If you set the second parameter to True, it
will return folder names also. Array comes back unsorted.

Public Function FileList(ByVal strPath As String, _
Optional includeFolders As Boolean = False)
As String()
Const iIncr As Long = 50

Dim strFileName As String
Dim strPath As String

Dim iSize As Long

Dim i As Long

Dim retVal() As String

iSize = iSize + iIncr
ReDim retVal(1 To iSize)

If Right$(strDir, 1) <> "\" Then strDir = strDir & "\"

strFileName = Dir(strDir & "*.*", _
IIf(includeFolders, vbDirectory, vbNormal))
Do While Len(strFileName) <> 0
If Left$(strFileName, 1) <> "." Then
i = i + 1
If i > iSize Then
iSize = iSize + iIncr
ReDim Preserve retVal(1 To iSize)
End If
retVal(i) = strFileName
End If
strFileName = Dir()
Loop

If i < iSize Then
ReDim Preserve retVal(1 To i)
End If

FileList = retVal
End Function

 

[Libby]

Many thanks

There is no FileSearch in Excel 2007 (or other Office 2007 programs).

You can use Dir. Here's a function I use that returns an array of
files in a directory. If you set the second parameter to True, it
will return folder names also. Array comes back unsorted.

Public Function FileList(ByVal strPath As String, _
Optional includeFolders As Boolean = False)
As String()
Const iIncr As Long = 50

Dim strFileName As String
Dim strPath As String

Dim iSize As Long

Dim i As Long

Dim retVal() As String

iSize = iSize + iIncr
ReDim retVal(1 To iSize)

If Right$(strDir, 1) <> "\" Then strDir = strDir & "\"

strFileName = Dir(strDir & "*.*", _
IIf(includeFolders, vbDirectory, vbNormal))
Do While Len(strFileName) <> 0
If Left$(strFileName, 1) <> "." Then
i = i + 1
If i > iSize Then
iSize = iSize + iIncr
ReDim Preserve retVal(1 To iSize)
End If
retVal(i) = strFileName
End If
strFileName = Dir()
Loop

If i < iSize Then
ReDim Preserve retVal(1 To i)
End If

FileList = retVal
End Function