I have recently updated to Excel2007. Great.!!! Several days ago I posted in
this forum a stupid post. I assumed my problems were due to 32 bit vs. 64
bit application. My bad.
Unfortunately I have several macros which have been operating since 2001
which depend on "application.filesearch". It has been eliminated in Excel
2007. So far, all of my search efforts have not found a work-around that can
be rapidly applied to my existing code.

Has anyone found a solution??

John

If you are searching a relatively small number of directories, you can
roll your own File Search using the Scripting FileSystemObject object.
The code below does this and runs rather quickly if you are not
searching many thousands of locations.

The values in the SearchControl Type dicatate how to run the search.
The params a

FileNameTemplate = a file name template with optional wildcards but no
folder information (e.g., "*.xls", not "C:\*.xls"). The file name is
tested with the Like operator, so use a compatible expression.

Count = initialize to 0. Upon completion contains the number of found
files.

CurrCount = initialize to 0. Used for smart array reallocation. No
meaningful info upon completion.

ArrReallocLimit = the number of elements between array reallocation.
Use 100 unless you have reason to change it.

Recurse = True to recurse through subfolders. False to search only the
specified folder.

FileNameOnly = True to return only file names with no path info (e.g.,
"Book1.xls"), False to return fully qualified file names (e.g.,
"C:\Test\Folder\Book1x.xls")

FSO = initialize to new instance Scripting.FileSystemObject.

FoundFiles = upon completion, an array of the found file names.

Put the following code in a reqular module:

'==========================================
' START CODE
'==========================================
Type SearchControl
FileNameTemplate As String
Count As Long
CurrCount As Long
ArrReallocLimit As Long
Recurse As Boolean
FileNameOnly As Boolean
FSO As Scripting.FileSystemObject
FoundFiles() As String
End Type


Sub XFileSearch(ByVal SearchFolder As Scripting.Folder, _
ByRef Data As SearchControl)
Dim FF As Scripting.Folder
Dim F As Scripting.File

With Data
For Each F In SearchFolder.Files
If F.Name Like .FileNameTemplate Then
.Count = .Count + 1
.CurrCount = .CurrCount + 1
If .FileNameOnly = True Then
.FoundFiles(.Count) = F.Name
Else
.FoundFiles(.Count) = F.Path
End If
If .CurrCount = .ArrReallocLimit Then
.CurrCount = 0
ReDim Preserve .FoundFiles _
(1 To .Count + .ArrReallocLimit)
End If
End If
Next F

If .Recurse = True Then
For Each FF In SearchFolder.SubFolders
XFileSearch FF, Data
Next FF
End If
End With
End Sub
'==========================================
' END CODE
'==========================================



You can the call the XFileSearch proc with code like the following:


'==========================================
' START CODE
'==========================================
Sub FileSearch()
Dim Data As SearchControl
Dim FirstFolder As Scripting.Folder
Dim N As Long

With Data
.ArrReallocLimit = 100
.Count = 0
.CurrCount = 0
.FileNameOnly = False
.FileNameTemplate = "*.xls"
Set .FSO = New Scripting.FileSystemObject
.Recurse = True
ReDim .FoundFiles(1 To .ArrReallocLimit)
End With

Set FirstFolder =
Data.FSO.GetFolder("C:\BinderListAndProductKeys")

XFileSearch FirstFolder, Data
With Data
If Data.Count 0 Then
ReDim Preserve .FoundFiles(1 To .Count)
Debug.Print "Files Found: " & Format(.Count, "#,##0")
For N = LBound(.FoundFiles) To UBound(.FoundFiles)
Debug.Print .FoundFiles(N)
Next N
Else
Debug.Print "no files found"
End If
End With
End Sub
'==========================================
' END CODE
'==========================================

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Thu, 5 Nov 2009 18:48:32 -0500, "John" wrote:

I have recently updated to Excel2007. Great.!!! Several days ago I posted in
this forum a stupid post. I assumed my problems were due to 32 bit vs. 64
bit application. My bad.
Unfortunately I have several macros which have been operating since 2001
which depend on "application.filesearch". It has been eliminated in Excel
2007. So far, all of my search efforts have not found a work-around that can
be rapidly applied to my existing code.

Has anyone found a solution??

John

I should have added that the code needs a reference to the Microsoft
Scripting Runtime. In VBA, go to the Tools menu, choose References,
scroll in the list to find "Microsoft Scripting Runtime" and check
that entry.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Thu, 05 Nov 2009 19:24:33 -0600, Chip Pearson
wrote:

If you are searching a relatively small number of directories, you can
roll your own File Search using the Scripting FileSystemObject object.
The code below does this and runs rather quickly if you are not
searching many thousands of locations.

The values in the SearchControl Type dicatate how to run the search.
The params a

FileNameTemplate = a file name template with optional wildcards but no
folder information (e.g., "*.xls", not "C:\*.xls"). The file name is
tested with the Like operator, so use a compatible expression.

Count = initialize to 0. Upon completion contains the number of found
files.

CurrCount = initialize to 0. Used for smart array reallocation. No
meaningful info upon completion.

ArrReallocLimit = the number of elements between array reallocation.
Use 100 unless you have reason to change it.

Recurse = True to recurse through subfolders. False to search only the
specified folder.

FileNameOnly = True to return only file names with no path info (e.g.,
"Book1.xls"), False to return fully qualified file names (e.g.,
"C:\Test\Folder\Book1x.xls")

FSO = initialize to new instance Scripting.FileSystemObject.

FoundFiles = upon completion, an array of the found file names.

Put the following code in a reqular module:

'==========================================
' START CODE
'==========================================
Type SearchControl
FileNameTemplate As String
Count As Long
CurrCount As Long
ArrReallocLimit As Long
Recurse As Boolean
FileNameOnly As Boolean
FSO As Scripting.FileSystemObject
FoundFiles() As String
End Type


Sub XFileSearch(ByVal SearchFolder As Scripting.Folder, _
ByRef Data As SearchControl)
Dim FF As Scripting.Folder
Dim F As Scripting.File

With Data
For Each F In SearchFolder.Files
If F.Name Like .FileNameTemplate Then
.Count = .Count + 1
.CurrCount = .CurrCount + 1
If .FileNameOnly = True Then
.FoundFiles(.Count) = F.Name
Else
.FoundFiles(.Count) = F.Path
End If
If .CurrCount = .ArrReallocLimit Then
.CurrCount = 0
ReDim Preserve .FoundFiles _
(1 To .Count + .ArrReallocLimit)
End If
End If
Next F

If .Recurse = True Then
For Each FF In SearchFolder.SubFolders
XFileSearch FF, Data
Next FF
End If
End With
End Sub
'==========================================
' END CODE
'==========================================



You can the call the XFileSearch proc with code like the following:


'==========================================
' START CODE
'==========================================
Sub FileSearch()
Dim Data As SearchControl
Dim FirstFolder As Scripting.Folder
Dim N As Long

With Data
.ArrReallocLimit = 100
.Count = 0
.CurrCount = 0
.FileNameOnly = False
.FileNameTemplate = "*.xls"
Set .FSO = New Scripting.FileSystemObject
.Recurse = True
ReDim .FoundFiles(1 To .ArrReallocLimit)
End With

Set FirstFolder =
Data.FSO.GetFolder("C:\BinderListAndProductKeys ")

XFileSearch FirstFolder, Data
With Data
If Data.Count 0 Then
ReDim Preserve .FoundFiles(1 To .Count)
Debug.Print "Files Found: " & Format(.Count, "#,##0")
For N = LBound(.FoundFiles) To UBound(.FoundFiles)
Debug.Print .FoundFiles(N)
Next N
Else
Debug.Print "no files found"
End If
End With
End Sub
'==========================================
' END CODE
'==========================================

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Thu, 5 Nov 2009 18:48:32 -0500, "John" wrote:

I have recently updated to Excel2007. Great.!!! Several days ago I posted in
this forum a stupid post. I assumed my problems were due to 32 bit vs. 64
bit application. My bad.
Unfortunately I have several macros which have been operating since 2001
which depend on "application.filesearch". It has been eliminated in Excel
2007. So far, all of my search efforts have not found a work-around that can
be rapidly applied to my existing code.

Has anyone found a solution??

John

Many thanks. It will take some time for me to absorb your code, and apply it
to my problem. Basically, I define a folder, and load the photos contained
in that folder to a worksheet. I have just discovered that Excel 2007 has
also eliminated "picture.insert". This is another problem for me to solve.
Excel 2000 is starting to look good.

Again, much thanks for your time and effort. It is going to be a great
learning experience.


John

'To check whether the file exists use Dir or file system object FSO.fileexists

'To list files within a folder try the below code
Sub FileList()
Dim strFile As string
Dim strFolder As string
strFolder = "c:\"

strFile = Dir(strFolder & "*.*", vbNormal)
Do While strFile < ""
MsgBox strFolder & strFile
strFile = Dir
Loop
End Sub

For recursion refer..
http://www.cpearson.com/excel/RecursionAndFSO.htm

'For an add-in that automatically lists folders, subfolders and files.
http://www.cpearson.com/excel/FolderTree.aspx


If this post helps click Yes
---------------
Jacob Skaria


"John" wrote:

I have recently updated to Excel2007. Great.!!! Several days ago I posted in
this forum a stupid post. I assumed my problems were due to 32 bit vs. 64
bit application. My bad.
Unfortunately I have several macros which have been operating since 2001
which depend on "application.filesearch". It has been eliminated in Excel
2007. So far, all of my search efforts have not found a work-around that can
be rapidly applied to my existing code.

Has anyone found a solution??

John