How to get files in directory

[AJA]

Hello
One problem.
I have to export data from all files in special directory. How to get name
of files in that directory and how to run macro in that files auromaticly ..
but not use function Auto_Open().
I need from one document (father.xls) run macro with get all files in
special directory, and from father.xls open step by step all files and run
in opened files macro ...

AJA

--
Serwis Usenet w portalu Gazeta.pl -> http://www.gazeta.pl/usenet/

[Jeff Mathews]

Here's something that does a very similar thing:

Dim fs As FileSearch
Set fs = Application.FileSearch
fs.NewSearch
fs.LookIn = "C:\Temp" 'Adjust to your needs
fs.FileType = msoFileTypeAllFiles 'Adjust to your needs
fs.Execute
For i = 1 To fs.FoundFiles.Count
Debug.Print fs.FoundFiles(i) 'Handle file here
Next i

Hope this helps.

You could also use the Dir() function - see the help for
details.

Dim myname As String
myname = Dir("C:\Temp\*.XLS")
Do Until myname = ""
'Handle file
myname = Dir() 'No parameter
Loop

With Dir you shouldn't add or delete files in the target
directory while you're enumerating it.

>.
>

[Jim Rech]

I've recently discovered that FileSearch does not always return all the
files it should so I will use Dir for the time being. Your advice re
deleting (or changing) files only after building a list of them is well
taken.

--
Jim Rech
Excel MVP

[Jeff Mathews]

Thanks for the heads up re FileSearch.

[Sheepish grin] I posted similar help a few days ago and
was warned by one of the MVPs about enumerating files in a
directory while deleting or adding files simultaneously.
He called it "unwise" and a little thought made that
obvious.

>.
>

[KL]

Jeff / Jim,

Can you tell what's wrong about FileSearch please? I have been using it
quite heavily to list files in a folder and have not noticed anithng.

Thanks a lot,
KL

Sub ListFiles()
Dim x As Integer
x = 1
Set fs = Application.FileSearch
With fs
.LookIn = Range("Directory").Value
.SearchSubFolders = False
.FileName = Range("Search").Value
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
ActiveSheet.Cells(x + 1, 1).Value = Dir(.FoundFiles(i))
x = x + 1
Next i
End If
End With
End Sub

"Jeff Mathews" <jeffam...@hotmail.com> wrote in message
news:749e01c263c5$42fc66e0$3aef2ecf@TKMSFTNGXA09...

[Jim Rech]

A month or two ago someone posted a question about why FileSearch didn't
return all the files that it should have. I had never had a problem with it
but hadn't used it all that much either. I asked what type of files were
not being found and he said there were a variety.

In experimenting, by accident I happened to pick a folder that had
subfolders containing my Favorites (a bunch of .URL files). Running this:

Sub a()
Dim Counter As Integer
With Application.FileSearch
.LookIn = "e:\ie"
.FileType = msoFileTypeAllFiles
.SearchSubFolders = True
.Execute
For Counter = 1 To .FoundFiles.Count
Cells(Counter, 1).Value = _
.FoundFiles(Counter)
Next
End With
End Sub

lists (I just tried it again now) 74 files. But there are 185 files in the
folder and subfolders. I can find no pattern to explain the missing files.
It's not the subfolder they are in or some aspect of their names like having
spaces or dashes, or file attributes.

I have also had a problem with ZIP files being skipped. Others have said
they have no problem with FS and zips and sometimes I do not, but just now I
did. Maybe it's something about Office XP. Or Windows XP...

So since I've lost confident in FS I use Dir.

[KL]

Thanks, Jim

Can you please post the code that uses Dir and that does the same as your
code below?

Many thanks,
KL

"Jim Rech" <jar...@kpmg.com> wrote in message
news:#r2P#c9YCHA.1460@tkmsftngp10...

[Jim Rech]

This is code that Bill Manville do some years ago. Seems to still work<g>:

Option Base 1
Dim aFiles() As String, iFile As Integer

Sub ListAllFilesInDirectoryStructure()
Dim Counter As Integer
iFile = 0
ListFilesInDirectory "c:\test\" ' change the top level as you wish

For Counter = 1 To iFile
Worksheets("Sheet1").Cells(Counter, 1).Value = aFiles(Counter)
Next

End Sub

Sub ListFilesInDirectory(Directory As String)
Dim aDirs() As String, iDir As Integer, stFile As String

' use Dir function to find files and directories in Directory
' look for directories and build a separate array of them
' note that Dir returns files as well as directories when vbDirectory
specified
iDir = 0
stFile = Directory & Dir(Directory & "*.*", vbDirectory)
Do While stFile <> Directory
If Right(stFile, 2) = "\." Or Right(stFile, 3) = "\.." Then
' do nothing - GetAttr doesn't like these directories
ElseIf GetAttr(stFile) = vbDirectory Then
' add to local array of directories
iDir = iDir + 1
ReDim Preserve aDirs(iDir)
aDirs(iDir) = stFile
Else
' add to global array of files
iFile = iFile + 1
ReDim Preserve aFiles(iFile)
aFiles(iFile) = stFile
End If
stFile = Directory & Dir()
Loop

' now, for any directories in aDirs call self recursively
If iDir > 0 Then
For iDir = 1 To UBound(aDirs)
ListFilesInDirectory aDirs(iDir) & Application.PathSeparator
Next iDir
End If
End Sub