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

 

[Bob Phillips]

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/software/dirlist/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

 

[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)

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 .

 

[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

 

[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]

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 lin
you gave me, and used the same command to 'crop' the displayed pat
file. Realistically that code would have worked, but like you said
the order in which file search returned the files was illogical, an
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 foru
software must have placed hard returns in your code, which turned i
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 har
returns to make the code functional again. But its the middle par
which threw me off, I couldn't get that to work. I figured I'd quot
the entire erroneous part though, just incase my own editting wa
wrong.

Also, I notice the hyperlink command is in there. Can you show me wha
it would look like without the hyperlink command? Thanks again.

I appreciate all the help I have received with this by the way. Yo
guys are making learning VBA a lot less stressful, as well as mor
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]

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 thi
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 i
seemed like it was displaying the list in the correct order. I als
noticed the 'long code' didn't use the .Filesearch feature. How can
incorporate the output of the long code to do what I want it to.
couldn't find a way to edit the code myself, thats the one that reall
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 a
generally known as when registering for online communities. The effec
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" spelle
creatively

 

[Bob Phillips]

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]

message


That is correct. AGain, that is relatively straight-forward, th
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 t
help
you with that. Is it just removing the hyperlinks,. or do you want th
date
as well (created, modified)?

The way the code works now is it displays all the files in a hierarch
spacing the file names to show what spreadsheets are 'under' the othe
spreadsheets. All I need is a straight list, one column, of all th
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.xl
-------------------



Does that make more sense? If not, I'll explain it more in depth late
today. Something has just come up for me. . .

Regardless though, thanks for following up with this. I sincerel
appreciate the help I have received from everyone. I can't say tha
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]

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


It still gives the flawed output you were talking about:


Code
-------------------
Sub thenextph8()
Dim sStartFolder As String
Dim iCtr As Long
Dim iLevel As Long
Dim iBaseLevel As Long
Dim sh As Worksheet
Dim iPath
sStartFolder = ThisWorkbook.path

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
iPath = Replace(.FoundFiles(iCtr), ThisWorkbook.path, "")
iLevel = Len(iPath) - Len(Replace(iPath, "\", ""))
sh.Cells(iCtr + 1, 1) = iPath
sh.Cells(iCtr + 1, 2).Value = iLevel
Next iCtr
End If
End With
End Su
-------------------


The code here (which is the same one Bob Phillips first posted
displays the files in the correct order, just the way it is displaye
isn't entirelly what I want. I have been trying for the life of me t
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 displa
the output the way I requested? I have been trying do this myself, bu
the results were unsuccesfull


Code
-------------------

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

 

[ph8]

Friendly and Shameless Bump. I hope you haven't given up on me just ye
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]

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