07-25-2005, 07:05 AM
|
|
|
|
Re: Recursive Function + File searching to return path
Straight-forward enough
Sub ph8()
Const sStartFolder As String = "c:\myTest"
Dim iCtr As Long
Dim iLevel As Long
Dim iBaseLevel As Long
Dim sh As Worksheet
iBaseLevel = Len(sStartFolder) - Len(Replace(sStartFolder, "\", ""))
With Application.FileSearch
.NewSearch
.LookIn = sStartFolder
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
On Error Resume Next
Set sh = Worksheets("Files")
On Error GoTo 0
If Not sh Is Nothing Then
sh.Cells.ClearContents
Else
Worksheets.Add.Name = "Files"
Set sh = ActiveSheet
End If
sh.Cells(1, 1) = sStartFolder
sh.Cells(1, 2) = 1
For iCtr = 1 To .FoundFiles.Count
iLevel = Len(.FoundFiles(iCtr)) - _
Len(Replace(.FoundFiles(iCtr), "\", ""))
sh.Cells(iCtr + 1, 1) = _
.FoundFiles(iCtr)
sh.Cells(iCtr + 1, 2).Value = iLevel
Next iCtr
End If
End With
End Sub
--
HTH
RP
(remove nothere from the email address if mailing direct)
"ph8" <ph8.1spred_1122275115.132@excelforum-nospam.com> wrote in message
news:ph8.1spred_1122275115.132@excelforum-nospam.com...
>
> The way the code works now is it displays all the files in a hierarchy
> spacing the file names to show what spreadsheets are 'under' the other
> spreadsheets. All I need is a straight list, one column, of all the
> spreadsheets. Instead of putting the file names in different columns,
> I want the that column number (or the Tier level) in the second column.
> Ideally, the output should look like this:
>
> Filename / Tier
> FileA.xls / 1
> FileB.xls / 2
> FileC.xls / 3
> FileD.xls / 3
> FileE.xls / 4
> FileF.xls / 4
> FileG.xls / 3
> FileH.xls / 2
> FileI.xls / 3
> FileJ.xls / 3
> FileK.xls / 3
>
> Code:
> --------------------
>
> Instead of the way the code currently outputs the file, which is:
> FileA.xls
> |--FileB.xls
> |--FileC.xls
> |--FileD.xls
> |--FileE.xls
> |--FileF.xls
> |--FileG.xls
> |--FileH.xls
> |--FileI.xls
> |--FileJ.xls
> |--FileK.xls
> --------------------
>
>
>
> Does that make more sense? If not, I'll explain it more in depth later
> today. Something has just come up for me. . .
>
> Regardless though, thanks for following up with this. I sincerely
> appreciate the help I have received from everyone. I can't say that
> enough. Thanks a lot.
>
>
> --
> ph8
> ------------------------------------------------------------------------
> ph8's Profile:
http://www.excelforum.com/member.php...o&userid=19871
> View this thread: http://www.excelforum.com/showthread...hreadid=387116
>
|