Aaaagh! this shouldn't be THIS hard!

[neo]

I'm just trying to get a simple directory listing of my hard drive into Excel 2000. I want to bring the list of folders, subfolders, and files into an Excel sheet. Simple enough, right?

I can get the files IN a particular folder to list themselves on the sheet using the Dir function and a loop, but I can't for the life of me figure out how to get the FOLDERS and SUBfolders to list. The closest thing I've found are the "folders" and "subfolders" properties, but I don't believe these apply...

I'm just trying to get this listing for my network so the backup operator can, at any time, run the macros in this workbook and get a directory listing of what's on a particular hard drive in an Excel format where it's easy to analyze (for choosing what to backup) and gives the admin a logfile to see what's being backed up.

Any help on this would be wonderful! I've been banging my head on a wall (or my screen, rather (c: ) for 2-1/2 days!!

Thanks,

neo

[ This Message was edited by: neo on 2002-03-13 09:23 ]

[ This Message was edited by: neo on 2002-03-13 09:25 ]

[Mark O'Brien]

Slap your code up and let us see what you've got. (or pull out the pertinent bits of code)

It doesn't sound like it should be too dificult.

[Joe Was]

Sub Look_In_x()
Dim lngCellCounter As Long
Dim Message, Title, Default, MyDir
'Search current directory for all files.

Application.ScreenUpdating = False
Message = "Enter the directory to search?" & Chr(13) & Chr(13) & "(Drive:DirectorySubDirectory)" ' Set prompt.
Title = "Enter: Drive and Path!" ' Set title.
Default = "H:Users" ' Set your default here first!.
' Display message, title, and default value.
On Error GoTo myErr

MyDir = InputBox(Message, Title, Default)

With Application.FileSearch
.NewSearch
.FileType = msoFileTypeExcelWorkbooks
.LookIn = MyDir
.SearchSubFolders = True
If .Execute() > 0 Then

MsgBox "There were " & .FoundFiles.Count & " file(s) found."

For lngCellCounter = 1 To .FoundFiles.Count
Cells(lngCellCounter, 1) = .FoundFiles(lngCellCounter)
Sheets("Sheet1").Select
Next lngCellCounter

Range("A1").Select
Selection.EntireRow.Insert
Range("AA2").Select
Selection.Copy
Range("A1").Select
ActiveSheet.Paste
Else

MsgBox "No Excel WorkBooks found!"
End If

End With
Application.ScreenUpdating = True
End
myErr:

MsgBox "No Excel WorkBooks found!"

End Sub

Note: This will list the found files on sheet1 cell A1 of the workbook using this code. Cell AA2 is a title for the displayed list, like: "These files found."
Sub Delete_Data()
'Delete the current screen print of file data.

Application.ScreenUpdating = False
Columns("A:A").Select
Selection.ClearContents
Range("A1").Select
Selection.EntireRow.Delete
Range("C1").Select
Application.ScreenUpdating = True
End Sub

This code will delete the list from Sheet1. JSW

[neo]

Joe!
I'm so sorry it took this long to respond, but Thank You dearly for your code! It worked perfectly! (of course, hehe)

Again, thank you (c:

neo

_________________
Check out Everquest. It's fun.

[ This Message was edited by: Juan Pablo G. on 2002-03-23 09:01 ]

[greg0226]

Hey Joe,

Just wanted to say that is some really nice code.

Greg

[casper_pkt]

Another person using your code years after your post. good stuff - thanks for sharing.