Search through subfolders
 

Given a parent folder, I need to be able to search through subfolders
looking for files with the extension ".mxd" and write the name of those
files to an excel spreadsheet.

Can anyone point me in the direction of any parts of this problem being
done elsewhere? (or tell me how its done?)

Search through subfolders
 

I came up with this using Application.FileSearch
Sub FindFiles()
Dim i As Long
With Application.FileSearch
.SearchSubFolders = True
.LookIn = "C:\"
.Filename = "*.txt"
.FileType = msoFileTypeAllFiles
.Execute
For i = 1 To .FoundFiles.Count
Range("A" & i) = .FoundFiles(i)
Next
End With
End Sub

I also found this on Ron De Bruin's site:
http://www.rondebruin.nl/copy33.htm

Charles

JustinP wrote:
Given a parent folder, I need to be able to search through subfolders
looking for files with the extension ".mxd" and write the name of those
files to an excel spreadsheet.

Can anyone point me in the direction of any parts of this problem being
done elsewhere? (or tell me how its done?)

Search through subfolders
 

Using the Scripting Runtime FileSystemObject is my preferred method...
It is included with Windows versions released after Windows 95.Microsoft Windows Script 5.6 Documentation...
http://msdn.microsoft.com/library/de...ist/webdev.asp

The free Excel add-in "List Files" does what you want.
Download from ... http://www.realezsites.com/bus/primitivesoftware
No registration required.
--
Jim Cone
San Francisco, USA


"JustinP"
wrote in message
Given a parent folder, I need to be able to search through subfolders
looking for files with the extension ".mxd" and write the name of those
files to an excel spreadsheet.

Can anyone point me in the direction of any parts of this problem being
done elsewhere? (or tell me how its done?)

Search through subfolders
 

This was posted by Jim Thomlinson in the past. (there have been many others
of similar ilk or using DIR)

Option Explicit
Option Compare Text


Sub test()
Call ListFiles("C:\Management", Sheet1.Range("A2"), "dbq", True)
End Sub


Public Sub ListFiles(ByVal strPath As String, _
ByVal rngDestination As Range, Optional ByVal strFileType As String = "*",
_
Optional ByVal blnSubDirectories As Boolean = False)
Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim strName As String


'Specify the file to look for...
strName = "*." & strFileType
Set objFSO = New Scripting.FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)


For Each objFile In objFolder.Files
If objFile.Name Like strName Then
rngDestination.Value = objFile.Path
rngDestination.Offset(0, 1).Value = objFile.DateLastAccessed
Set rngDestination = rngDestination.Offset(1, 0)
End If
Next 'objFile
Set objFile = Nothing


'Call recursive function
If blnSubDirectories = True Then _
DoTheSubFolders objFolder.SubFolders, rngDestination, strName


Set objFSO = Nothing
Set objFolder = Nothing
End Sub


Function DoTheSubFolders(ByRef objFolders As Scripting.Folders, _
ByRef rng As Range, ByRef strTitle As String)
Dim scrFolder As Scripting.Folder
Dim scrFile As Scripting.File
Dim lngCnt As Long


On Error GoTo ErrorHandler
For Each scrFolder In objFolders
For Each scrFile In scrFolder.Files
If scrFile.Name Like strTitle Then
rng.Value = scrFile.Path
rng.Offset(0, 1).Value = scrFile.DateLastAccessed
Set rng = rng.Offset(1, 0)
End If
Next 'scrFile


'If there are more sub folders then go back and run function again.
If scrFolder.SubFolders.Count 0 Then
DoTheSubFolders scrFolder.SubFolders, rng, strTitle
End If
ErrorHandler:
Next 'scrFolder


Set scrFile = Nothing
Set scrFolder = Nothing
End Function




--

Regards,

Tom Ogilvy



"JustinP" wrote in message
ups.com...
Given a parent folder, I need to be able to search through subfolders
looking for files with the extension ".mxd" and write the name of those
files to an excel spreadsheet.

Can anyone point me in the direction of any parts of this problem being
done elsewhere? (or tell me how its done?)

Search through subfolders
 

Thanks for your help guys. It is much appreciated.


Tom Ogilvy wrote:
This was posted by Jim Thomlinson in the past. (there have been many others
of similar ilk or using DIR)

Option Explicit
Option Compare Text


Sub test()
Call ListFiles("C:\Management", Sheet1.Range("A2"), "dbq", True)
End Sub


Public Sub ListFiles(ByVal strPath As String, _
ByVal rngDestination As Range, Optional ByVal strFileType As String = "*",
_
Optional ByVal blnSubDirectories As Boolean = False)
Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim strName As String


'Specify the file to look for...
strName = "*." & strFileType
Set objFSO = New Scripting.FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)


For Each objFile In objFolder.Files
If objFile.Name Like strName Then
rngDestination.Value = objFile.Path
rngDestination.Offset(0, 1).Value = objFile.DateLastAccessed
Set rngDestination = rngDestination.Offset(1, 0)
End If
Next 'objFile
Set objFile = Nothing


'Call recursive function
If blnSubDirectories = True Then _
DoTheSubFolders objFolder.SubFolders, rngDestination, strName


Set objFSO = Nothing
Set objFolder = Nothing
End Sub


Function DoTheSubFolders(ByRef objFolders As Scripting.Folders, _
ByRef rng As Range, ByRef strTitle As String)
Dim scrFolder As Scripting.Folder
Dim scrFile As Scripting.File
Dim lngCnt As Long


On Error GoTo ErrorHandler
For Each scrFolder In objFolders
For Each scrFile In scrFolder.Files
If scrFile.Name Like strTitle Then
rng.Value = scrFile.Path
rng.Offset(0, 1).Value = scrFile.DateLastAccessed
Set rng = rng.Offset(1, 0)
End If
Next 'scrFile


'If there are more sub folders then go back and run function again.
If scrFolder.SubFolders.Count 0 Then
DoTheSubFolders scrFolder.SubFolders, rng, strTitle
End If
ErrorHandler:
Next 'scrFolder


Set scrFile = Nothing
Set scrFolder = Nothing
End Function




--

Regards,

Tom Ogilvy



"JustinP" wrote in message
ups.com...
Given a parent folder, I need to be able to search through subfolders
looking for files with the extension ".mxd" and write the name of those
files to an excel spreadsheet.

Can anyone point me in the direction of any parts of this problem being
done elsewhere? (or tell me how its done?)