Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
All Subdirectories
I would love to spend the time to figure this out on my own but I am in a bit
of a time crunch. Does anyone have some code for search a given directory and all subdirectories for a specific file type (*.xls). Using file system object is my preference but I am not too fussy. I would like to return the file name and path. -- HTH... Jim Thomlinson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
All Subdirectories
Hi Jim,
Why reinvent the wheel. This ought to work... Regards, Jim Cone San Francisco, USA Microsoft Windows Script 5.6 Documentation http://msdn.microsoft.com/library/de...ist/webdev.asp '---------------------------------- Option Explicit Option Compare Text Sub ListFoldersAndSubFolderAndFiles() Jim Cone - San Francisco, USA - May 24, 2005/July,02, 2005 'Requires a project reference to "Microsoft Scripting Runtime" (scrrun.dll) 'List all files and folders in the specified folder. Dim objFSO As Scripting.FileSystemObject Dim objFolder As Scripting.Folder Dim objFile As Scripting.File Dim strPath As String Dim strName As String Dim lngNum As Long 'Specify the folder... strPath = "C:\Documents and Settings" 'Specify the file to look for... strName = "*.xls" Set objFSO = New Scripting.FileSystemObject Set objFolder = objFSO.GetFolder(strPath) lngNum = 2 For Each objFile In objFolder.Files If objFile.Name Like strName Then Cells(lngNum, 2) = objFile.Path lngNum = lngNum + 1 End If Next 'objFile Set objFile = Nothing 'Call recursive function DoTheSubFolders objFolder.SubFolders, lngNum, strName Set objFSO = Nothing Set objFolder = Nothing End Sub '------------------------ Function DoTheSubFolders(ByRef objFolders As Scripting.Folders, _ ByRef lngN As Long, ByRef strTitle As String) Dim scrFolder As Scripting.Folder Dim scrFile As Scripting.File Dim lngCnt As Long For Each scrFolder In objFolders For Each scrFile In scrFolder.Files If scrFile.Name Like strTitle Then Cells(lngN, 2).Value = scrFile.Path lngN = lngN + 1 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, lngN, strTitle End If Next 'scrFolder Set scrFile = Nothing Set scrFolder = Nothing End Function '------------------- "Jim Thomlinson" wrote in message ... I would love to spend the time to figure this out on my own but I am in a bit of a time crunch. Does anyone have some code for search a given directory and all subdirectories for a specific file type (*.xls). Using file system object is my preference but I am not too fussy. I would like to return the file name and path.-- HTH... Jim Thomlinson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
All Subdirectories
Thanks. I had the first sub but I did not have the recursive section... That
saves time... -- HTH... Jim Thomlinson "Jim Cone" wrote: Hi Jim, Why reinvent the wheel. This ought to work... Regards, Jim Cone San Francisco, USA Microsoft Windows Script 5.6 Documentation http://msdn.microsoft.com/library/de...ist/webdev.asp '---------------------------------- Option Explicit Option Compare Text Sub ListFoldersAndSubFolderAndFiles() Jim Cone - San Francisco, USA - May 24, 2005/July,02, 2005 'Requires a project reference to "Microsoft Scripting Runtime" (scrrun.dll) 'List all files and folders in the specified folder. Dim objFSO As Scripting.FileSystemObject Dim objFolder As Scripting.Folder Dim objFile As Scripting.File Dim strPath As String Dim strName As String Dim lngNum As Long 'Specify the folder... strPath = "C:\Documents and Settings" 'Specify the file to look for... strName = "*.xls" Set objFSO = New Scripting.FileSystemObject Set objFolder = objFSO.GetFolder(strPath) lngNum = 2 For Each objFile In objFolder.Files If objFile.Name Like strName Then Cells(lngNum, 2) = objFile.Path lngNum = lngNum + 1 End If Next 'objFile Set objFile = Nothing 'Call recursive function DoTheSubFolders objFolder.SubFolders, lngNum, strName Set objFSO = Nothing Set objFolder = Nothing End Sub '------------------------ Function DoTheSubFolders(ByRef objFolders As Scripting.Folders, _ ByRef lngN As Long, ByRef strTitle As String) Dim scrFolder As Scripting.Folder Dim scrFile As Scripting.File Dim lngCnt As Long For Each scrFolder In objFolders For Each scrFile In scrFolder.Files If scrFile.Name Like strTitle Then Cells(lngN, 2).Value = scrFile.Path lngN = lngN + 1 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, lngN, strTitle End If Next 'scrFolder Set scrFile = Nothing Set scrFolder = Nothing End Function '------------------- "Jim Thomlinson" wrote in message ... I would love to spend the time to figure this out on my own but I am in a bit of a time crunch. Does anyone have some code for search a given directory and all subdirectories for a specific file type (*.xls). Using file system object is my preference but I am not too fussy. I would like to return the file name and path.-- HTH... Jim Thomlinson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
All Subdirectories
Look at the thread DOS....there must be a better way, dated 8 Oct 2005.
RBS "Jim Thomlinson" wrote in message ... I would love to spend the time to figure this out on my own but I am in a bit of a time crunch. Does anyone have some code for search a given directory and all subdirectories for a specific file type (*.xls). Using file system object is my preference but I am not too fussy. I would like to return the file name and path. -- HTH... Jim Thomlinson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
All Subdirectories
FYI, I ran into one very small glitch. I do not have access to one of the
subdirectories. Added this little tidbit of code... Seems to be running again... Function DoTheSubFolders(ByRef objFolders As Scripting.Folders, _ ByRef lngN As Long, 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 Cells(lngN, 2).Value = scrFile.Path lngN = lngN + 1 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, lngN, strTitle End If Next 'scrFolder ErrorHandler: Set scrFile = Nothing Set scrFolder = Nothing End Function -- HTH... Jim Thomlinson "Jim Cone" wrote: Hi Jim, Why reinvent the wheel. This ought to work... Regards, Jim Cone San Francisco, USA Microsoft Windows Script 5.6 Documentation http://msdn.microsoft.com/library/de...ist/webdev.asp '---------------------------------- Option Explicit Option Compare Text Sub ListFoldersAndSubFolderAndFiles() Jim Cone - San Francisco, USA - May 24, 2005/July,02, 2005 'Requires a project reference to "Microsoft Scripting Runtime" (scrrun.dll) 'List all files and folders in the specified folder. Dim objFSO As Scripting.FileSystemObject Dim objFolder As Scripting.Folder Dim objFile As Scripting.File Dim strPath As String Dim strName As String Dim lngNum As Long 'Specify the folder... strPath = "C:\Documents and Settings" 'Specify the file to look for... strName = "*.xls" Set objFSO = New Scripting.FileSystemObject Set objFolder = objFSO.GetFolder(strPath) lngNum = 2 For Each objFile In objFolder.Files If objFile.Name Like strName Then Cells(lngNum, 2) = objFile.Path lngNum = lngNum + 1 End If Next 'objFile Set objFile = Nothing 'Call recursive function DoTheSubFolders objFolder.SubFolders, lngNum, strName Set objFSO = Nothing Set objFolder = Nothing End Sub '------------------------ Function DoTheSubFolders(ByRef objFolders As Scripting.Folders, _ ByRef lngN As Long, ByRef strTitle As String) Dim scrFolder As Scripting.Folder Dim scrFile As Scripting.File Dim lngCnt As Long For Each scrFolder In objFolders For Each scrFile In scrFolder.Files If scrFile.Name Like strTitle Then Cells(lngN, 2).Value = scrFile.Path lngN = lngN + 1 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, lngN, strTitle End If Next 'scrFolder Set scrFile = Nothing Set scrFolder = Nothing End Function '------------------- "Jim Thomlinson" wrote in message ... I would love to spend the time to figure this out on my own but I am in a bit of a time crunch. Does anyone have some code for search a given directory and all subdirectories for a specific file type (*.xls). Using file system object is my preference but I am not too fussy. I would like to return the file name and path.-- HTH... Jim Thomlinson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
All Subdirectories
Jim,
You can run into errors ... 1. with the system volume folder - you have to stay away from it 2. with the "If scrFolder.SubFolders.Count 0", it errors if there are no subfolders. You either have to trap errors at the appropriate point or put a blanket on error resume next in the code. Regards, Jim Cone "Jim Thomlinson" wrote in message FYI, I ran into one very small glitch. I do not have access to one of the subdirectories. Added this little tidbit of code... Seems to be running again... Function DoTheSubFolders(ByRef objFolders As Scripting.Folders, _ ByRef lngN As Long, 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 Cells(lngN, 2).Value = scrFile.Path lngN = lngN + 1 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, lngN, strTitle End If Next 'scrFolder ErrorHandler: Set scrFile = Nothing Set scrFolder = Nothing End Function-- HTH... Jim Thomlinson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
All Subdirectories
Does anyone know where this is? I can't seem to find it. I searched all
Office posts and still couldn't find it. -- Sue Programmer/Data Analyst Minnesota "RB Smissaert" wrote: Look at the thread DOS....there must be a better way, dated 8 Oct 2005. RBS |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
All Subdirectories
http://tinyurl.com/ysv8eo
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... Does anyone know where this is? I can't seem to find it. I searched all Office posts and still couldn't find it. -- Sue Programmer/Data Analyst Minnesota "RB Smissaert" wrote: Look at the thread DOS....there must be a better way, dated 8 Oct 2005. RBS |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
All Subdirectories
Thanks, Bob!
-- Sue Programmer/Data Analyst Minnesota "Bob Phillips" wrote: http://tinyurl.com/ysv8eo -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
All Subdirectories
"Bob Phillips" wrote...
http://tinyurl.com/ysv8eo .... While I did read that the OP in that thread wanted an alternative to console commands run via Shell, it's difficult to believe piping the output from DIR through FINDSTR and possibly then through SORT wouldn't have produced the desired result much more quickly than the VBA alternative. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
All Subdirectories
It's been a long time since I did piping. I need to program a command button
in Excel to do a search in all our system directories for all Access databases. I want the output to be dumped into Excel and separated into columns (Name, Location, etc.) -- Sue Programmer/Data Analyst Minnesota "Harlan Grove" wrote: "Bob Phillips" wrote... http://tinyurl.com/ysv8eo .... While I did read that the OP in that thread wanted an alternative to console commands run via Shell, it's difficult to believe piping the output from DIR through FINDSTR and possibly then through SORT wouldn't have produced the desired result much more quickly than the VBA alternative. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
list of all subdirectories in a given directory in excel | Excel Worksheet Functions | |||
Subdirectories | Excel Programming |