|
Recursive Function + File searching to return path
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 ). 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 |
Recursive Function + File searching to return path
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" wrote in message ... 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 ). 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 |
Recursive Function + File searching to return path
"ph8" wrote in message
... 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 |
Recursive Function + File searching to return path
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 |
Recursive Function + File searching to return path
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" wrote in message ... 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 |
Recursive Function + File searching to return path
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. |
Recursive Function + File searching to return path
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. -- ph8 ------------------------------------------------------------------------ ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871 View this thread: http://www.excelforum.com/showthread...hreadid=387116 |
Recursive Function + File searching to return path
"ph8" wrote in message ... 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. |
Recursive Function + File searching to return path
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 : -- ph ----------------------------------------------------------------------- ph8's Profile: http://www.excelforum.com/member.php...fo&userid=1987 View this thread: http://www.excelforum.com/showthread.php?threadid=38711 |
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" wrote in message ... 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 |
Recursive Function + File searching to return path
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 -- ph ----------------------------------------------------------------------- ph8's Profile: http://www.excelforum.com/member.php...fo&userid=1987 View this thread: http://www.excelforum.com/showthread.php?threadid=38711 |
Recursive Function + File searching to return path
"ph8" wrote in message ... 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! |
Recursive Function + File searching to return path
Bob Phillips Wrote: "ph8" wrote i message ... I compared the code to the previous code you gave me ("the lon 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. also noticed the 'long code' didn't use the .Filesearch feature. How ca 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 tha really confused me when I tried to understand it =/ 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 -- ph ----------------------------------------------------------------------- ph8's Profile: http://www.excelforum.com/member.php...fo&userid=1987 View this thread: http://www.excelforum.com/showthread.php?threadid=38711 |
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" wrote in message ... 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 |
Recursive Function + File searching to return path
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 -------------------- Please, any help is appreciated. -- ph8 ------------------------------------------------------------------------ ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871 View this thread: http://www.excelforum.com/showthread...hreadid=387116 |
Recursive Function + File searching to return path
Friendly and Shameless Bump. I hope you haven't given up on me just ye Bob Phillips :( Again, any help would be greatly appreciated -- ph ----------------------------------------------------------------------- ph8's Profile: http://www.excelforum.com/member.php...fo&userid=1987 View this thread: http://www.excelforum.com/showthread.php?threadid=38711 |
Recursive Function + File searching to return path
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" wrote in message ... 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 |
Recursive Function + File searching to return path
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 |
Recursive Function + File searching to return path
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" wrote in message ... 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 |
Recursive Function + File searching to return path
Sounds good Bob, I look forward to your next post. v/r -Eddie -- ph8 ------------------------------------------------------------------------ ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871 View this thread: http://www.excelforum.com/showthread...hreadid=387116 |
Recursive Function + File searching to return path
Friendly Bump for Bob -- ph8 ------------------------------------------------------------------------ ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871 View this thread: http://www.excelforum.com/showthread...hreadid=387116 |
Recursive Function + File searching to return path
Please, this project is so close to being finished, I could really us your help again, Bob -- or anyone willing to provide assistance -- ph ----------------------------------------------------------------------- ph8's Profile: http://www.excelforum.com/member.php...fo&userid=1987 View this thread: http://www.excelforum.com/showthread.php?threadid=38711 |
Recursive Function + File searching to return path
I guess Ill post again in hopes of getting Bob's attention one las time. I'll remain optimistic and trust Bob will still get to it as h has told me. Further help would be greatly appreciated Bob :) -- ph ----------------------------------------------------------------------- ph8's Profile: http://www.excelforum.com/member.php...fo&userid=1987 View this thread: http://www.excelforum.com/showthread.php?threadid=38711 |
Recursive Function + File searching to return path
Bumping this thread out of ancient history. Maybe Bob will find it thi time.. -- ph ----------------------------------------------------------------------- ph8's Profile: http://www.excelforum.com/member.php...fo&userid=1987 View this thread: http://www.excelforum.com/showthread.php?threadid=38711 |
| All times are GMT +1. The time now is 01:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com