Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Aaaagh! this shouldn't be THIS hard!

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Birmingham, Al
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Birmingham, Al
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    Benicia, CA
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey Joe,

    Just wanted to say that is some really nice code.

    Greg

  6. #6
    New Member
    Join Date
    Aug 2007
    Location
    Atlanta, GA.
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Code still in use

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •