|
Re: Recursive Function + File searching to return path
"ph8" <ph8.1sh1j3_1121868774.0477@excelforum-nospam.com> wrote in message
news:ph8.1sh1j3_1121868774.0477@excelforum-nospam.com...
> I used the 'ThisWorkbook.Path' suggestion instead of C:\ in the
> original code. This code pretty much outputs all the .xls files in the
> directory along with the file date. What it lacks is the 'tier' level I
> tried to describe earlier. I was thinking, we could crop the initial
> path off the output by having it search for 'ThisWorkbook.path' in the
> output, and cropping that part (since they will all have the same
> 'ThisWorkbook.path'). I don't entirely know the commands for that, but
> we can get to that in a minute. The 'tier' can be found easily by
> counting the backslashes in the rest of the output path. This I'm sure
> involves the same command that I'll need to crop the root path from the
> full path names to all the files. I'll look through the excel help
> files to see if I can figure this out, any help is appreciated though.
You can easily count the backslashes by taking the length of the filepath
sans backslash from the total path length
iLevel = Len(.FoundFiles(iCtr)) - Len(Replace(.FoundFiles(iCtr), "\", ""))
> ===========================
> The other code, the longer one, was really cool.
Glad you like it, I like it too :-)
> It put hyperlinks to
> all the files and organized them in the heirarchy they were already in.
> That was rather impressive heh. Regardless, I think this one might be
> easyer to modify to what I want. Instead of outputting the 'sub'
> sheets in the next column, it should just up the 'tier' counter and
> output that in the 2nd column with the file path in the first column.
> The hyperlink part can be removed (although that was really nifty).
The other code can be easily modified to do the same, although the order is
not logical to me in the way that Filesearch retrieves them
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"
End If
For iCtr = 1 To .FoundFiles.Count
iLevel = Len(.FoundFiles(iCtr)) -
Len(Replace(.FoundFiles(iCtr), "\", ""))
sh.Hyperlinks.Add Anchor:=sh.Cells(iCtr, (iLevel -
iBaseLevel) * 2 - 1), _
Address:=.FoundFiles(iCtr), _
TextToDisplay:=.FoundFiles(iCtr)
sh.Cells(iCtr, (iLevel - iBaseLevel) * 2).Value =
FileDateTime(.FoundFiles(iCtr))
Next iCtr
End If
End With
End Sub
>
> This one though I had an exceptionally hard time trying to decipher. I
> followed the code very vaguely, but it was well beyond my knowledge. Is
> there any chance someone can provide an explination about this? What
> part does what? It doesn't have to be super thorough, but at least
> enough so I can get the basics of what the purpose of each
> function/sub/variable is, and I can go from there.
This one is actually the easiest as it hands off to the system, and just
outputs the results.
Filesearch does what is says on the label, it searches for files, and stores
mall matches in a collection that you can interrogate.
The first part just defines the search criteria, where to start, what type
of file to look for, etc.
Then it executes the search, and checks if there are any matches.
It then dumps all matches into a worksheet, using the Filecount to know when
to stop.
Note that this code does not use recursion like mine and Tushar's. The
Filesearch may well do (probably does), but not this code itself.
|