Filesearch Errors

[T. Jenkins]

This is my first time developing/editing macros since switching to Office
2007, so I'm wondering if I have a setting wrong someplace.

I'm trying to run a routine I used in the past to list all files in a
particular folder in a worksheet. Because I was having trouble getting this
to run, I searched the Excel help, and found the sample code below, which I
used to test the basic filesearch functionality. Since I'm getting the same
error, it's clear that something is wrong.

The code below errors-out at the initial line, "With
Application.Filesearch." The error is as follows: Run-time error 445: Object
doesn't support this action." This is the same error I'm getting in my
earlier code.

Do I have something configured wrong here? I would tell you the specifics
about the version I'm using, but since MS no longer seems to have a Help,
About option, I can't find it. I just know its Office 2007.

Thanks for the help.

=========== sample code ===========
With Application.FileSearch
.NewSearch
.LookIn = "J:\My Documents\__Print"
.SearchSubFolders = True
.Filename = "run"
.TextOrProperty = "San*"
.MatchAllWordForms = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

 

[Ron de Bruin]

They removed it from 2007

You can use Dir or FSO
See
http://www.rondebruin.nl/copy3.htm

Or also for subfolders
http://www.rondebruin.nl/fso.htm

 

[Don Guillett]

Sub listfilesusingDIR()
Dim FN As String
'FN = Dir(ThisWorkbook.Path & "\*.*")
FN = Dir("C:\a\*.xls")
r = 1
Do Until FN = ""
'MsgBox FN
Cells(r, 1) = FN
r = r + 1
FN = Dir
Loop
End Sub

 

[T. Jenkins]

Thanks very much for the response. Weird that they did not include it from
the help.

It looks like your code is designed to consolidate data from different
spreadsheets into one file. Sine all I need is paths and filenames, I would
need to modify your code pretty significantly. Can you give me any quick
tips on editing it to just enter the path and filenames for all files located
in a given folder/subfolder? I could probably figure it out, but since you
will know the code very well, I thought you might be able to help.

I'm just planning to use this to help find files with exceptionally long
paths, so I can take action to address path length errors. All I need is the
path/filename in an excel list, and I can take it from there.

Thanks again,
Todd

 

[Ron de Bruin]

I have a small .bat file for this

Open a new file in notepad and copy the code below in it

@echo off
dir %1 /-p /b /o:gn /s > "%temp%\Listing.txt"
start notepad "%temp%\Listing.txt"
exit

Then save the txt file and close it
Then change the extension to .bat

When you double click the bat file it will create a txt file for you with the whole list of files
You can copy it in Excel if you want from there

If you mail me I can send the bat to you

 

[Ron de Bruin]

Btw: copy/open the file in the root folder you want

You can use the same bat file in any location you want