How to find all folder and/or files in a specific location

[Mr. GetRight]

I want to make a macro that reads folder names in a particular path. I am
saving files to path F:\Documents\School- FL\CBT Tracker VB project . If
user inputs year as 2008 a new folder is made at that location and the
workbook is save under the new folder with the teachers. When the user comes
back to the prompt screen I wanted to add a list box that allows them to
select the year, and another one to select the teachers name. How can i do
this?


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...61aea24&dg=microsoft.public.excel.programming

 

[Franz Erhart]

The following macro scans the current path and all sub dirs and looks if
there are any Excel files.
If it finds an Excel files it opens and subsequently closes the file again.
Inbetween you can add your code as needed



Sub BuildCRList()

Dim iRow As Integer
Dim cWorkBookName As String

cWorkBookName = "*" & Application.ActiveWindow.Caption & "*"

With Application.FileSearch ' get name
of all Excel workbooks
.LookIn = Application.ActiveWorkbook.Path ' give root
path
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = True ' scan
subdirectories if true

If .Execute() > 0 Then ' start if
Excel files found
For iRow = 1 To .FoundFiles.Count Step 1 ' loop
through all workbooks
If Not (.FoundFiles(iRow) Like cWorkBookName) Then ' don't call
yourself
If (Tabelle3.CB_Check_Run.Value) Then ' only if
summary is wanted
Workbooks.Open FileName:=.FoundFiles(iRow) ', ReadOnly:=True
Else
Workbooks.Open FileName:=.FoundFiles(iRow), ReadOnly:=True
End If

' ... Here your code ...

ActiveWindow.Close SaveChanges:=False
End If
Next iRow
End If
End With
End Sub