Aaaagh! this shouldn't be THIS hard!

neo

New Member
Joined
Mar 6, 2002
Messages
19
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,<marquee behavior="alternate">neo</marquee>
This message was edited by neo on 2002-03-13 09:23
This message was edited by neo on 2002-03-13 09:25
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.
 
Upvote 0
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
 
Upvote 0
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:<marquee behavior="alternate">neo</marquee>

_________________
Check out Everquest. It's fun.
This message was edited by Juan Pablo G. on 2002-03-23 09:01
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top