07-20-2005, 09:39 AM
|
|
Registered User
|
|
Join Date: 13 Feb 2005
Posts: 66
|
|
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.
Quote:
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.
|