Sensing files in a directory

[leydorfs]

1. I have a group of excel files in a directory. They all contain a workbook named "projects". I don't know how many files will be in the directory.
I'd like to write a VBA script to detect each file, then reference a specific cell from the workbook "projects" in each file. How can this be done.

2. If so, I'd like to recurse subdirectories of the file, and reference the same cell.

Thanks.

 

[lutz]

Hello guys, hello NateO!

At first let me thank you for your code. I was googling a way to replace the filesearch method in excel 2010 and successfully implemented your code to make it work now. I used Filesearch before and use your code now to scan all (about 3200) PDF Files in 9 Subfolders on a network drive. (see code below but it is pretty much the one that you posted). So again I am glad that it works at all now, BUT the filesearch method was way faster since I was measuring execution times of about 2 seconds. With the code below I measure now execution times of about 80seconds. ok, I experienced that any code performed in Excel 2010 is slower than in Excel 2000 on the same machine but only by factor of 1 to 1.5 and not factor of 40

So my question being:
Is there any way to speed up this algoritm? I would appreciate it very much!

Dim fso As Object
Dim strName As String
Const strDir As String = "O:\02_AA_FA_ED_PA\"
Const searchTerm As String = "*"

Let strName = Dir$(strDir & "\*" & "*.pdf")
Do While strName <> vbNullString
Let i = i + 1
Let strArr(i, 1) = strDir & "\" & strName
Let strName = Dir$()
Loop
Set fso = CreateObject("Scripting.FileSystemObject")
Call recurseSubFolders(fso.GetFolder(strDir), strArr(), i, searchTerm)
Set fso = Nothing

Private Sub recurseSubFolders(ByRef Folder As Object, _
ByRef strArr() As String, _
ByRef i As Long, _
ByRef searchTerm As String)
Dim SubFolder As Object
Dim strName As String
For Each SubFolder In Folder.SubFolders
Let strName = Dir$(SubFolder.Path & "\*" & searchTerm & "*.pdf")
Do While strName <> vbNullString
Let i = i + 1
Let strArr(i, 1) = SubFolder.Path & "\" & strName
Let strName = Dir$()
Loop
Call recurseSubFolders(SubFolder, strArr(), i, searchTerm)
Next
End Sub

 

[prakashnpk]

I have executed the piece of code which I replaced for Application.File search.
Getting below compilation error,

Compiler Error
ByRef argument type mismatch with the control in VBA code shown below,

Call recurseSubFolders(fso.GetFolder(strDir), strArr(), i, searchTerm)