Looping through folders and using the first file in each folder

[Conan Kelly]

Hello all,

How do I loop through folders and get the first file from each folder?

I have code that will loop through a set of folders, gets the column labels out of the first file in each folder and lists them in
the current workbook. The problem is that I'm getting the first file in each folder by using it's file name. If the files in each
folder are named the same, then this won't work correctly.

Isn't there a way to get the first file without having to use the file name?

Here is the code:


Sub List_FileName_ColumnHeaders()
Dim fso As New FileSystemObject
Dim fsoFolder As Folder
Dim fsoSubFolder As Folder
Dim fsoFile As File

Dim pstrCol1 As String
...
Dim pstrCol16 As String

pstrCol1 = ""
...
pstrCol16 = ""

Set fsoFolder = fso.GetFolder("X:\Some Folder\Data Files")

For Each fsoSubFolder In fsoFolder.SubFolders


Set fsoFile = fsoSubFolder.Files("2003-08_Aug 2003.csv")
'***This is where I'm having problems. ***'
'***"Set fsoFile = fsoSubFolder.Files(1)" does not work ***'
'***When I use the "...fsoSubFolder.Files(1)", I get an ***'
'***"Invalid procedure call or argument".


ActiveCell = fsoFile.Name

Open fsoFile For Input As #1

Input #1, pstrCol1, ... , pstrCol16

Cells(ActiveCell.Row, ActiveCell.Column + 1) = pstrCol1
...
Cells(ActiveCell.Row, ActiveCell.Column + 16) = pstrCol16

Close #1

Cells(ActiveCell.Row + 1, ActiveCell.Column).Select

Next fsoSubFolder



End Sub

 

[Jim Cone]

Conan,
Couple of items...
Windows will not allow multiple files with the same name
in the same folder. If you know the file name why is there a problem?

How do you define the first file? The files could be sorted in any order.
Are you looking for the first file in an alpha sorted list?
Are you looking for the earliest file date or ?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Conan Kelly" <CTBarbarin at msn dot com>
wrote in message
Hello all,
How do I loop through folders and get the first file from each folder?
I have code that will loop through a set of folders, gets the column labels
out of the first file in each folder and lists them in the current workbook.
The problem is that I'm getting the first file in each folder by using it's file name.
If the files in each folder are named the same, then this won't work correctly.

Isn't there a way to get the first file without having to use the file name?
Here is the code:

Sub List_FileName_ColumnHeaders()
Dim fso As New FileSystemObject
Dim fsoFolder As Folder
Dim fsoSubFolder As Folder
Dim fsoFile As File

Dim pstrCol1 As String
...
Dim pstrCol16 As String

pstrCol1 = ""
...
pstrCol16 = ""

Set fsoFolder = fso.GetFolder("X:\Some Folder\Data Files")

For Each fsoSubFolder In fsoFolder.SubFolders

Set fsoFile = fsoSubFolder.Files("2003-08_Aug 2003.csv")
'***This is where I'm having problems. ***'
'***"Set fsoFile = fsoSubFolder.Files(1)" does not work ***'
'***When I use the "...fsoSubFolder.Files(1)", I get an ***'
'***"Invalid procedure call or argument".

ActiveCell = fsoFile.Name

Open fsoFile For Input As #1

Input #1, pstrCol1, ... , pstrCol16

Cells(ActiveCell.Row, ActiveCell.Column + 1) = pstrCol1
...
Cells(ActiveCell.Row, ActiveCell.Column + 16) = pstrCol16

Close #1

Cells(ActiveCell.Row + 1, ActiveCell.Column).Select

Next fsoSubFolder

End Sub

 

[Greg Glynn]

Conan,

Are you wanting to open the OLDEST file in the folder? Is that what
you mean by the FIRST? Or is it the first ALPHABETICALLY?

You can get the OLDEST like this ...
From: http://j-walk.com/ss/excel/tips/tip97.htm

Option Base 1
Function OldestFile(Directory, FileSpec)
' Returns the full path and name of the most recent file in a Directory
' That matches the FileSpec (e.g., "*.xls").
' Returns an empty string if the directory does not exist or
' it contains no matching files
Dim NumFound As Long
NewestFile = ""
With Application.FileSearch
.NewSearch
.LookIn = Directory
.FileName = FileSpec
NumFound = .Execute(SortBy:=msoSortByLastModified, _
SortOrder:=msoSortOrderAscending)
If NumFound > 0 Then OldestFile = .FoundFiles(1)
End With
End Function

And call the function: MyOldFile = OldestFile("C:\","*.*")