07-21-2005, 08:05 AM
|
|
|
|
Re: Recursive Function + File searching to return path
Yeah, the NG does that often. The trick is anticipating where, I usually get
it wrong :-)
Here is how that code should look
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))
hopefully this will not wrap.
I added the hyperlinks because you seemed to like it, and it was realtively
simple. It can of course be removed, just use
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
For iCtr = 1 To .FoundFiles.Count
iLevel = Len(.FoundFiles(iCtr)) - _
Len(Replace(.FoundFiles(iCtr), "\", ""))
sh.Cells(iCtr, (iLevel - iBaseLevel) * 2 - 1) = _
.FoundFiles(iCtr)
sh.Cells(iCtr, (iLevel - iBaseLevel) * 2).Value = _
FileDateTime(.FoundFiles(iCtr))
Next iCtr
End If
End With
End Sub
BTW, what does ph8 stand for?
--
HTH
RP
(remove nothere from the email address if mailing direct)
"ph8" <ph8.1sifig_1121933157.9592@excelforum-nospam.com> wrote in message
news:ph8.1sifig_1121933157.9592@excelforum-nospam.com...
>
> Thanks for your help Bob. I modifyed your code with the replace line
> you gave me, and used the same command to 'crop' the displayed path
> file. Realistically that code would have worked, but like you said,
> the order in which file search returned the files was illogical, and
> unfortunately unusable for the intent I want to use it for.
>
> I played with your code, but unfortunately couldn't get it to work.
> The reason for this is the code did not copy correctly. The forum
> software must have placed hard returns in your code, which turned it
> all red in the VBA editor.
> >
> > 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))
> >
>
> Some parts were obvious and I was able to manually delete the hard
> returns to make the code functional again. But its the middle part
> which threw me off, I couldn't get that to work. I figured I'd quote
> the entire erroneous part though, just incase my own editting was
> wrong.
>
> Also, I notice the hyperlink command is in there. Can you show me what
> it would look like without the hyperlink command? Thanks again.
>
> I appreciate all the help I have received with this by the way. You
> guys are making learning VBA a lot less stressful, as well as more
> entertaining 
>
>
> --
> ph8
> ------------------------------------------------------------------------
> ph8's Profile:
http://www.excelforum.com/member.php...o&userid=19871
> View this thread: http://www.excelforum.com/showthread...hreadid=387116
>
|