Recursive Function + File searching to return path

[ph8]

Hey folks, I need a little help.

First and foremost, does Excel VBA support recursive functions/subs? (IE: can I call a sub from within the same sub?)

Second. Does VBA have a way to browse through file directories? I have a file Hierarchy, and what I would love is for Excel to start at the top and for every .xls file it finds, put it in a list in the spreadsheet calling the 'search' macro. It should return the path of the file name. Since these spreadsheets are nested within directories, which are within directories, which are within directories, etc...

In reality, I need a sub that starts in the directory where the file calling/initiating the sub is saved in. From there, it looks for (other) .xls files, if it doesn't find any, it will look in the first directory it sees. From that directory, it will look for a .xls file, if it finds one, it needs to return the path to that .xls file and add it to the next available spot on sheet2 of the initiating file. From there it will search the next directory for excel files or directories and keep going. Until all directories have been searched and all 'excel' files have been reported for.

I don't entirely know if this makes any sense. I hope it does. It seemed like it would be more difficult to explain, which leads me to think I am missing something big.

Anyways this seems like it would be easier if I could 'chat' to someone about this live over AIM or whatnot (because I may have other questions as i am going). I don't suppose any excel experts have some sort of chat handle/program that they can use while going about their business (I don't want to be an inconvenience). If you don't want to give it out publicly, please e-mail me with details ([email protected]). Thanks in advanced.

I would appreciate any assistance or ideas with the issues above as well (on these forums). Thanks again!

[Damon Longworth]

Yes, you can call a sub from in a sub.


Trythis to get a file list:

With Application.FileSearch
.NewSearch
'Could use ThisWorkbook.Path in LookIn
.LookIn = "c:\"
.SearchSubFolders = True
'.Filename = "book1.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
For iCtr = 1 To .FoundFiles.Count
Cells(iCtr, 1).Value = .FoundFiles(iCtr)
Cells(iCtr, 2).Value = FileDateTime(.FoundFiles(iCtr))
Next iCtr
End If
End With

--
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com


"ph8" <[email protected]> wrote in message
news:[email protected]...
>
> Hey folks, I need a little help.
>
> First and foremost, does Excel VBA support recursive functions/subs?
> (IE: can I call a sub from within the same sub?)
>
> Second. Does VBA have a way to browse through file directories? I have
> a file Hierarchy, and what I would love is for Excel to start at the
> top and for every .xls file it finds, put it in a list in the
> spreadsheet calling the 'search' macro. It should return the path of
> the file name. Since these spreadsheets are nested within directories,
> which are within directories, which are within directories, etc...
>
> In reality, I need a sub that starts in the directory where the file
> calling/initiating the sub is saved in. From there, it looks for
> (other) .xls files, if it doesn't find any, it will look in the first
> directory it sees. From that directory, it will look for a .xls file,
> if it finds one, it needs to return the path to that .xls file and add
> it to the next available spot on sheet2 of the initiating file. From
> there it will search the next directory for excel files or directories
> and keep going. Until all directories have been searched and all
> 'excel' files have been reported for.
>
> I don't entirely know if this makes any sense. I hope it does. It
> seemed like it would be more difficult to explain, which leads me to
> think I am missing something big.
>
> Anyways this seems like it would be easier if I could 'chat' to someone
> about this live over AIM or whatnot (because I may have other questions
> as i am going). I don't suppose any excel experts have some sort of
> chat handle/program that they can use while going about their business
> (I don't want to be an inconvenience). If you don't want to give it out
> publicly, please e-mail me with details ([email protected]). Thanks in
> advanced.
>
> I would appreciate any assistance or ideas with the issues above as
> well (on these forums). Thanks again!
>
>
> --
> ph8
> ------------------------------------------------------------------------
> ph8's Profile:
> http://www.excelforum.com/member.php...o&userid=19871
> View this thread: http://www.excelforum.com/showthread...hreadid=387116
>

[Bob Phillips]

"ph8" <[email protected]> wrote in message
news:[email protected]...
>
> Hey folks, I need a little help.
>
> First and foremost, does Excel VBA support recursive functions/subs?
> (IE: can I call a sub from within the same sub?)

Yes, no problem as long as you are careful to ensure exits and termination
(but that is recursion, not Excel or VBA).

> Second. Does VBA have a way to browse through file directories? I have
> a file Hierarchy, and what I would love is for Excel to start at the
> top and for every .xls file it finds, put it in a list in the
> spreadsheet calling the 'search' macro. It should return the path of
> the file name. Since these spreadsheets are nested within directories,
> which are within directories, which are within directories, etc...

Yes it does. I have done this sort of thing many times before.

Option Explicit

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As Long


Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long


Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Private FSO As Object
Private cnt As Long
Private arfiles
Private level As Long


Sub Folders()
Dim i As Long
Dim sFolder As String
Dim sh As Worksheet

Set FSO = CreateObject("Scripting.FileSystemObject")

arfiles = Array()
cnt = -1
level = 1

sFolder = GetFolder
ReDim arfiles(1, 0)
If sFolder <> "" Then
SelectFiles sFolder
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
With ActiveSheet
For i = LBound(arfiles, 2) To UBound(arfiles, 2)
.Hyperlinks.Add Anchor:=.Cells(i + 1, arfiles(1, i)), _
Address:=arfiles(0, i), _
TextToDisplay:=arfiles(0, i)
Next
.Columns("A:Z").EntireColumn.AutoFit
End With
End If

End Sub


'-----------------------------*------------------------------*------------
Sub SelectFiles(Optional sPath As String)
'-----------------------------*------------------------------*------------
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object


If sPath = "" Then
Set FSO = CreateObject("Scripting.FileSystemObject")
sPath = GetFolder
End If

Set Folder = FSO.GetFolder(sPath)

Set Files = Folder.Files
For Each file In Files
cnt = cnt + 1
ReDim Preserve arfiles(1, cnt)
arfiles(0, cnt) = Folder.path & "\" & file.Name
arfiles(1, cnt) = level
Next file

level = level + 1
For Each fldr In Folder.Subfolders
SelectFiles fldr.path
Next
level = level - 1

End Sub


'-----------------------------*------------------------------*--
Function GetFolder(Optional ByVal Name As String = _
"Select a folder.") As String
'-----------------------------*------------------------------*--
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

bInfo.pidlRoot = 0& 'Root folder = Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to

oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function

[Tushar Mehta]

Yes, VBA supports recursion.

Check the VBA Dir function, the Office FileSearch object, and the
Windows Script FileSystemObject.

If you want a list of all files, check the add-in:
Directory List
http://www.tushar-mehta.com/excel/so...ist/index.html

It generates a list of all files. in a directory (and sub-directories).
The enhanced features are available as shareware.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
>
> Hey folks, I need a little help.
>
> First and foremost, does Excel VBA support recursive functions/subs?
> (IE: can I call a sub from within the same sub?)
>
> Second. Does VBA have a way to browse through file directories? I have
> a file Hierarchy, and what I would love is for Excel to start at the
> top and for every .xls file it finds, put it in a list in the
> spreadsheet calling the 'search' macro. It should return the path of
> the file name. Since these spreadsheets are nested within directories,
> which are within directories, which are within directories, etc...
>
> In reality, I need a sub that starts in the directory where the file
> calling/initiating the sub is saved in. From there, it looks for
> (other) .xls files, if it doesn't find any, it will look in the first
> directory it sees. From that directory, it will look for a .xls file,
> if it finds one, it needs to return the path to that .xls file and add
> it to the next available spot on sheet2 of the initiating file. From
> there it will search the next directory for excel files or directories
> and keep going. Until all directories have been searched and all
> 'excel' files have been reported for.
>
> I don't entirely know if this makes any sense. I hope it does. It
> seemed like it would be more difficult to explain, which leads me to
> think I am missing something big.
>
> Anyways this seems like it would be easier if I could 'chat' to someone
> about this live over AIM or whatnot (because I may have other questions
> as i am going). I don't suppose any excel experts have some sort of
> chat handle/program that they can use while going about their business
> (I don't want to be an inconvenience). If you don't want to give it out
> publicly, please e-mail me with details ([email protected]). Thanks in
> advanced.
>
> I would appreciate any assistance or ideas with the issues above as
> well (on these forums). Thanks again!
>
>
> --
> ph8
> ------------------------------------------------------------------------
> ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871
> View this thread: http://www.excelforum.com/showthread...hreadid=387116
>
>

[ph8]

Thanks for the help folks, I appreciate it.

I do have more questions though, if you all would be so kind...

I looked at the Add in link, and I don't think I will be able to use this. Then end goal for this system is to place it on a network drive for many users to access. I don't want to force all users to download and install an add on (especially since some/most of these users will be computer illiterate).

Otherwise both the VBA codes provided in responses seem to be the way I want to go. The problem is both those codes surpass my VBA knowledge. I don't entirely know what does what and how it all works (and as such, how to use it). Could anyone please explain the code and what it does. Ideally I wanted a macro to go through directories and make an output list of all the directories and files it finds. I think the best way to explain this would be with an example. Imagine this is the Hierarchy:

Tier 1 Filename: A.xls
--Tier 2 Filename: B.xls
----Tier 3 Filename: C.xls
----Tier 3 Filename: D.xls
------Tier 4 Filename: E.xls
------Tier 4 Filename: F.xls
------Tier 4 Filename: G.xls
----Tier 3 Filename: H.xls
----Tier 3 Filename: I.xls
--Tier 2 Filename: J.xls
----Tier 3 Filename: K.xls
----Tier 3 Filename: L.xls
----Tier 3 Filename: M.xls
--Tier 2 Filename: N.xls
----Tier 3 Filename: O.xls
------Tier 4 Filename: P.xls
------Tier 4 Filename: Q.xls
----Tier 3 Filename: R.xls

Ok. Bear with me, I'm sorry if this doesn't make sense. This 'file finding' spreadsheet is going to be in a folder which contains another folder. That other folder contains the entire hierarchy. The way the Hierarchy itself is organized is the folder you click on will contain a excel sheet with the same name as the folder its contained in. In that folder will also be the folder for the next level sheets for the hierarchy. IE: the first 2 tiers should look like this.

\\NetworkDrive\FileFinder.xls
\\NetworkDrive\A [dir]
\\NetworkDrive\A\A.xls
\\NetworkDrive\A\B [dir]
\\NetworkDrive\A\B\B.xls
\\NetworkDrive\A\B\C [dir]
\\NetworkDrive\A\B\D [dir]
\\NetworkDrive\A\B\H [dir]
\\NetworkDrive\A\B\I [dir]
\\NetworkDrive\A\J [dir]
\\NetworkDrive\A\N [dir]

I hope that makes sense.

In any case, each spreadsheet has a distinct filename and they all have a 'tier' value. The recursive function I was planning on writing (since VBA can handle recursion -- thanks for the answer by the way, folks!) was a spreadsheet that starts at the FileFinder.xls level, then goes to the first Excel Find it finds, which will be A.xls. It then needs to output the excel filename (with or without .xls extension, preferably without) to the first available row in a column I will have reserved for this output in the FileFinder function. After that it needs to look in the A.xls directory and it will see the next directories. It should go into that directory (and bump the tier counter to 2) and output the XLS file it finds, which will be B.xls. The output column will actually be two columns. Both the tier and the filename will be output. From B it will look for another directory, and find C, which will be output with tier 3. From the "C" directory, it won't find any other directories before it, which will be the termination/end for the recursive function. It will drop the Tier back to 2, and go back to the B directory, where it will find the next directory which will be "D". ETC... all the way down the list.

For the Hierarchy above, the output should resemble exactly this:
FileName / Tier
A 1
B 2
C 3
D 3
E 4
F 4
G 4
H 3
I 3
J 2
K 3
L 3
M 3
N 2
O 3
P 4
Q 4
R 3

Does that make any sense? I hope so.

Either way, I haven't exactly started any coding work for this, so I'd be open to other suggestions if the more experienced VBA users know a simpler way to accomplish this. I am considering axing the recursive part completely and just update the 'output' manually as I add/remove spreadsheets to the hierarchy. I just figured this way would be simpler.

After typing this all out its dawned on me. I think I would rather code this myself, but I definitely need help. I don't know how to frankly. I guess all I am trying to say is, in this case I would rather be taught to fish then be given a fish. . . if you catch my drift.

Again though, thanks everyone for your time and help. These forums have never let me down .

[Bob Phillips]

I haven't read all of your post (life is too short :-)).

Why don't you do some of the work, try the code, and then come back and tell
us what it does that you don't want/ what it doesn't do that you do want.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ph8" <[email protected]> wrote in message
news:[email protected]...
>
> Thanks for the help folks, I appreciate it.
>
> I do have more questions though, if you all would be so kind...
>
> I looked at the Add in link, and I don't think I will be able to use
> this. Then end goal for this system is to place it on a network drive
> for many users to access. I don't want to force all users to download
> and install an add on (especially since some/most of these users will
> be computer illiterate).
>
> Otherwise both the VBA codes provided in responses seem to be the way I
> want to go. The problem is both those codes surpass my VBA knowledge.
> I don't entirely know what does what and how it all works (and as such,
> how to use it). Could anyone please explain the code and what it does.
> Ideally I wanted a macro to go through directories and make an output
> list of all the directories and files it finds. I think the best way
> to explain this would be with an example. Imagine this is the
> Hierarchy:
>
> Tier 1 Filename: A.xls
> --Tier 2 Filename: B.xls
> ----Tier 3 Filename: C.xls
> ----Tier 3 Filename: D.xls
> ------Tier 4 Filename: E.xls
> ------Tier 4 Filename: F.xls
> ------Tier 4 Filename: G.xls
> ----Tier 3 Filename: H.xls
> ----Tier 3 Filename: I.xls
> --Tier 2 Filename: J.xls
> ----Tier 3 Filename: K.xls
> ----Tier 3 Filename: L.xls
> ----Tier 3 Filename: M.xls
> --Tier 2 Filename: N.xls
> ----Tier 3 Filename: O.xls
> ------Tier 4 Filename: P.xls
> ------Tier 4 Filename: Q.xls
> ----Tier 3 Filename: R.xls
>
> Ok. Bear with me, I'm sorry if this doesn't make sense. This 'file
> finding' spreadsheet is going to be in a folder which contains another
> folder. That other folder contains the entire hierarchy. The way the
> Hierarchy itself is organized is the folder you click on will contain a
> excel sheet with the same name as the folder its contained in. In that
> folder will also be the folder for the next level sheets for the
> hierarchy. IE: the first 2 tiers should look like this.
>
> \\NetworkDrive\FileFinder.xls
> \\NetworkDrive\A -[dir]-
> \\NetworkDrive\A\A.xls
> \\NetworkDrive\A\B -[dir]-
> \\NetworkDrive\A\B\B.xls
> \\NetworkDrive\A\B\C -[dir]-
> \\NetworkDrive\A\B\D -[dir]-
> \\NetworkDrive\A\B\H -[dir]-
> \\NetworkDrive\A\B\I -[dir]-
> \\NetworkDrive\A\J -[dir]-
> \\NetworkDrive\A\N -[dir]-
>
> I hope that makes sense.
>
> In any case, each spreadsheet has a distinct filename and they all have
> a 'tier' value. The recursive function I was planning on writing (since
> VBA can handle recursion -- thanks for the answer by the way, folks!)
> was a spreadsheet that starts at the FileFinder.xls level, then goes to
> the first Excel Find it finds, which will be A.xls. It then needs to
> output the excel filename (with or without .xls extension, preferably
> without) to the first available row in a column I will have reserved
> for this output in the FileFinder function. After that it needs to
> look in the A.xls directory and it will see the next directories. It
> should go into that directory (and bump the tier counter to 2) and
> output the XLS file it finds, which will be B.xls. The output column
> will actually be two columns. Both the tier and the filename will be
> output. From B it will look for another directory, and find C, which
> will be output with tier 3. From the "C" directory, it won't find any
> other directories before it, which will be the termination/end for the
> recursive function. It will drop the Tier back to 2, and go back to
> the B directory, where it will find the next directory which will be
> "D". ETC... all the way down the list.
>
> For the Hierarchy above, the output should resemble exactly this:
> FileName / Tier
> A 1
> B 2
> C 3
> D 3
> E 4
> F 4
> G 4
> H 3
> I 3
> J 2
> K 3
> L 3
> M 3
> N 2
> O 3
> P 4
> Q 4
> R 3
>
> Does that make any sense? I hope so.
>
> Either way, I haven't exactly started any coding work for this, so I'd
> be open to other suggestions if the more experienced VBA users know a
> simpler way to accomplish this. I am considering axing the recursive
> part completely and just update the 'output' manually as I add/remove
> spreadsheets to the hierarchy. I just figured this way would be
> simpler.
>
> After typing this all out its dawned on me. I think I would rather
> code this myself, but I definitely need help. I don't know how to
> frankly. I guess all I am trying to say is, in this case I would
> rather be taught to fish then be given a fish. . . if you catch my
> drift.
>
> Again though, thanks everyone for your time and help. These forums
> have never let me down .
>
>
> --
> ph8
> ------------------------------------------------------------------------
> ph8's Profile:
http://www.excelforum.com/member.php...o&userid=19871
> View this thread: http://www.excelforum.com/showthread...hreadid=387116
>

[Gareth]

And just to encourage you further to follow Bob's advice about rolling
your sleeves up (and catching some fish)... don't be put off by your
network / addin installation concerns. There are many ways to deploy
this, a few examples:

(a) Run as an AddIn on the network - just give your users a shortcut to
the AddIn and all they need to is doubleclick, enable macros (if set to
security is set to Medium) and away they go.

(b) Install it as an AddIn, still on the network - not locally
(otherwise upgrading is a pig). This way it will be available as soon as
Excel starts, there are no security flags to worry about. (You can even
use VBA to install the AddIn for you - so you can minimise time spent
with the end users.)

(c) Place your code in a workbook, on the network. Have the users access
that.

(d) Place your code in a workbook in Outlook Public Folders....

etc. etc.

YOu get the picture. Deployment shouldn't be a problem.

HTH
Gareth

Bob Phillips wrote:
> I haven't read all of your post (life is too short :-)).
>
> Why don't you do some of the work, try the code, and then come back and tell
> us what it does that you don't want/ what it doesn't do that you do want.
>

[ph8]

Ok. I took your advice and went a head and tried it. They both worked for me. And in truth I was rather impressed. VBA gets more and more diverse every time I learn more about it. But thats besides the point...

I need some explination though, because in truth, I don't know how the code above does what it does.

Sub dunno1()

Dim iCtr

With Application.FileSearch
.NewSearch
'Could use ThisWorkbook.Path in LookIn
.LookIn = ThisWorkbook.path
.SearchSubFolders = True
'.Filename = "book1.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
For iCtr = 1 To .FoundFiles.Count
Cells(iCtr, 1).Value = .FoundFiles(iCtr)
Cells(iCtr, 2).Value = FileDateTime(.FoundFiles(iCtr))
Next iCtr
End If
End With

End Sub

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.

===========================
The other code, the longer one, was really cool. 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).

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.

[Bob Phillips]

"ph8" <[email protected]> wrote in message
news:[email protected]...
> 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.

[ph8]

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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[ph8]

Great, I got the code working and I edited to more fit my needs a tad. But I noticed it wasn't outputting the files in any logical order. Which again was missing the reason why I needed this list in this format to begin with.

I compared the code to the previous code you gave me ("the long one") and realized they were different. I re ran the first code, and it seemed like it was displaying the list in the correct order. I also noticed the 'long code' didn't use the .Filesearch feature. How can I incorporate the output of the long code to do what I want it to. I couldn't find a way to edit the code myself, thats the one that really confused me when I tried to understand it =/

Thanks again,
-Eddie

PS: ph8 is nothing more than an alias I have grown used to. Its I am generally known as when registering for online communities. The effect its suppose to create is the F sound from 'ph' with the word eight (8) said out loud. To put it bluntly, its the word "Fate" spelled creatively.

[Bob Phillips]

"ph8" <[email protected]> wrote in message
news:[email protected]...
>
> Great, I got the code working and I edited to more fit my needs a tad.
> But I noticed it wasn't outputting the files in any logical order.
> Which again was missing the reason why I needed this list in this
> format to begin with.

I mentioned that in an earier post, but I guess my message got confused and
you must have thought mine didn't get the order correct. Filesearch must be
accessing based upon some other attribute rather than name.

> I compared the code to the previous code you gave me ("the long one")
> and realized they were different. I re ran the first code, and it
> seemed like it was displaying the list in the correct order. I also
> noticed the 'long code' didn't use the .Filesearch feature. How can I
> incorporate the output of the long code to do what I want it to. I
> couldn't find a way to edit the code myself, thats the one that really
> confused me when I tried to understand it =/

That is correct. AGain, that is relatively straight-forward, the complexity
is mainly in the browse folder functionality, but if you isolate that, it is
fairly simple. What amendment do you want to make, I should be able to help
you with that. Is it just removing the hyperlinks,. or do you want the date
as well (created, modified)?

> PS: ph8 is nothing more than an alias I have grown used to. Its I am
> generally known as when registering for online communities. The effect
> its suppose to create is the F sound from 'ph' with the word eight (8)
> said out loud. To put it bluntly, its the word "Fate" spelled
> creatively.

LOL!

[ph8]

"ph8" <[email protected]> wrote in message
news:[email protected]...

> I compared the code to the previous code you gave me ("the long one")
> and realized they were different. I re ran the first code, and it
> seemed like it was displaying the list in the correct order. I also
> noticed the 'long code' didn't use the .Filesearch feature. How can I
> incorporate the output of the long code to do what I want it to. I
> couldn't find a way to edit the code myself, thats the one that really
> confused me when I tried to understand it =/

That is correct. AGain, that is relatively straight-forward, the complexity
is mainly in the browse folder functionality, but if you isolate that, it is
fairly simple. What amendment do you want to make, I should be able to help
you with that. Is it just removing the hyperlinks,. or do you want the date
as well (created, modified)?

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

Please Login or Register  to view this content.

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.

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[ph8]

Maybe its something that I am doing wrong. But I still can't get it to work as intended. Here is the code after I make my modifications to it. In truth, all I do is shorten the display of the output and have the 'backslash' search feature only look in the truncated path instead.

It still gives the flawed output you were talking about:

Please Login or Register  to view this content.

The code here (which is the same one Bob Phillips first posted) displays the files in the correct order, just the way it is displayed isn't entirelly what I want. I have been trying for the life of me to try and interpret this code but I can't seem to fully understand it. Most likely because there are so many 'new' commands to me in the code. Regardless, wouldn't it be simplest to just modify this code to display the output the way I requested? I have been trying do this myself, but the results were unsuccesfull

Please Login or Register  to view this content.

Please, any help is appreciated.

[ph8]

Friendly and Shameless Bump. I hope you haven't given up on me just yet Bob Phillips

Again, any help would be greatly appreciated.

[Bob Phillips]

Sorry mate, only just seen this follow-up.

What is not happening that you want to happen?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ph8" <[email protected]> wrote in message
news:[email protected]...
>
> Friendly and Shameless Bump. I hope you haven't given up on me just yet
> Bob Phillips
>
> Again, any help would be greatly appreciated.
>
>
> --
> ph8
> ------------------------------------------------------------------------
> ph8's Profile:
http://www.excelforum.com/member.php...o&userid=19871
> View this thread: http://www.excelforum.com/showthread...hreadid=387116
>

[ph8]

What is not happening that you want to happen?

The longer code is producing the list in the correct order. The shorter code is producing the output correctly, but the order is still skewed. Ideally, I would like the longer code's output in the format of the shorter code's output.

Does this make sense?

PS: Thanks for keeping with me. I appreciate it.

[Bob Phillips]

ph8,

I need to leave now, but just to let you know I will re-look at this
tomorrow. Hopefully, we will see a conclusion then.

Regards

Bob


"ph8" <[email protected]> wrote in message
news:[email protected]...
>
> Bob Phillips Wrote:
> > What is not happening that you want to happen?
> >
> The longer code is producing the list in the correct order. The
> shorter code is producing the output correctly, but the order is still
> skewed. Ideally, I would like the longer code's output in the format
> of the shorter code's output.
>
> Does this make sense?
>
> PS: Thanks for keeping with me. I appreciate it.
>
>
> --
> ph8
> ------------------------------------------------------------------------
> ph8's Profile:
http://www.excelforum.com/member.php...o&userid=19871
> View this thread: http://www.excelforum.com/showthread...hreadid=387116
>

[ph8]

Sounds good Bob, I look forward to your next post.

v/r
-Eddie

[ph8]

Friendly Bump for Bob

[ph8]

Please, this project is so close to being finished, I could really use your help again, Bob -- or anyone willing to provide assistance.

[ph8]

I guess Ill post again in hopes of getting Bob's attention one last time. I'll remain optimistic and trust Bob will still get to it as he has told me. Further help would be greatly appreciated Bob .

[ph8]

Bumping this thread out of ancient history. Maybe Bob will find it this time...