Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Extract File List from Explorer to Excel?

This is a discussion on Extract File List from Explorer to Excel? within the Excel Questions forums, part of the Question Forums category; Does anyone know if the file list in Explorer can be ‘extracted’ electronically to Excel? I’m working in a Win ...

  1. #1
    New Member
    Join Date
    Mar 2003
    Location
    Dublin, Ireland
    Posts
    41

    Default Extract File List from Explorer to Excel?

    Does anyone know if the file list in Explorer can be ‘extracted’ electronically to Excel? I’m working in a Win XP environment. Must be a way but ……. finding it isn’t easy (I tried searching across previous postings/replies). Any help greatly appreciated!

  2. #2
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: Extract File List from Explorer to Excel?

    See this recent thread for extracting file details from a specified directory:

    http://www.mrexcel.com/board2/viewto...r=asc&start=10

    Post back if you want a non-macro method.

    Regards,

    Mike

  3. #3
    New Member
    Join Date
    Mar 2003
    Location
    Dublin, Ireland
    Posts
    41

    Default

    Quote Originally Posted by Ekim
    See this recent thread for extracting file details from a specified directory:

    http://www.mrexcel.com/board2/viewto...r=asc&start=10

    Post back if you want a non-macro method.

    Regards,

    Mike
    Many thanks Ekim, I can see this issue affects members regularly!! My company's latest PCs are configured to prevent users downloading software and I note that DOS isn't available either -that's the first 2 suggestions taken care of. I then tried the macro and it worked fine. However, I don't know VBA at all and can't get it to pick up ALL files in the folder (tried filetype = FileTypeAllFiles but got 'no files found'). Is there a simple answer to this? Finally, you mention another MS Outlook option - can you fill me on this please (Outlook 2002)? Cyril

  4. #4
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: Extract File List from Explorer to Excel?

    The following is a repeat of what I posted at http://www.mrexcel.com/board2/viewto...r=asc&start=10 with some amendments.

    All you have to do is to change the stuff between the double lines, specifically:

    Your path – include the quote marks around the path and the final back slash.
    Filetype – if you want all files put “*”; if you want just Excel files, put “xls”; zipped files – “zip” etc.
    Worksheet reference i.e. where the data will go. Currently set as Sheet2 (if this is OK, then leave as is).
    Startrow this is the row from which your data will start. Currently set as row 2 (if this is OK, then leave as is).

    The macro is set to show all files. Just change the path of where your files are located.
    Code:
    Sub ListFiles2()
    Dim fileList() As String
    Dim fName As String
    Dim fPath As String
    Dim i As Integer
    Dim startrow As Integer
    Dim ws As Worksheet
    Dim filetype  As String
    
    '=======================================================
    fPath = "C:\Temp\"
    filetype = "*"
    Set ws = Worksheets("Sheet2")
    startrow = 2    'starting row for the data
    '========================================================
    
    fName = Dir(fPath & "*." & filetype)
    While fName <> ""
        i = i + 1
        ReDim Preserve fileList(1 To i)
        fileList(i) = fName
        fName = Dir()
    Wend
    If i = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
        
    For i = 1 To UBound(fileList)
        ws.Range("A" & i + startrow).Value = fileList(i)
    Next
    Columns(1).AutoFit
    
    End Sub
    See this line in the macro:
    fName = Dir(fPath & "*." & filetype)

    When the above line incorporates the first two defined variables (in-between the double lines above), it becomes:

    fName = Dir(“C:\Temp\*.*”)

    i.e. pick up all files.

    The next post is the non-macro method.

    HTH

    Mike

  5. #5
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: Extract File List from Explorer to Excel?

    Here’s a non-macro way that requires MS Outlook and MS Excel (using Outlook 2002 and Excel 2002).

    1. Open Excel and Outlook.
    2. Switch to Outlook.
    3. On the left side-menu, select “Other Shortcuts”
    4. One the next left side-menu, select “My Documents”.
    5. On the right side Explorer type menu structure, navigate to the directory that contains your files (double click the desired directory icon).
    6. Select all the files with Ctrl-A (hold down the Ctrl key, then hit the keyboard key for the letter A; it does not matter if you use upper case “A” or lower case “a”).
    7. After selecting the files, copy them to the clipboard by doing Ctrl-C (hold down the Ctrl key, then hit the keyboard key for the letter C).
    8. Switch to Excel and select cell A1 in a blank worksheet.
    9. Copy the files from the clipboard to Excel by doing Ctrl-V.
    10. Delete any superfluous Excel columns.

    Enhancements:
    After step 5 above, in Outlook, right click the header bar (the one that has “Name”, “Author”, “Size” etc.). Experiment with the menu choices - “Field Chooser” and “Customize Current View”, particularly the option to filter the files to copy to Excel.

    HTH

    Mike

  6. #6
    New Member
    Join Date
    Mar 2003
    Location
    Dublin, Ireland
    Posts
    41

    Default Re: Extract File List from Explorer to Excel?

    What can I say? that's just brilliant. Many thanks for such a prompt and comprehensive response. Best Regards, Cyril

  7. #7
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,883

    Default Re: Extract File List from Explorer to Excel?

    Slick!
    Quote Originally Posted by Ekim
    Here’s a non-macro way that requires MS Outlook and MS Excel (using Outlook 2002 and Excel 2002).

    {snip}
    Mike

  8. #8
    New Member
    Join Date
    Feb 2010
    Posts
    4

    Default Re: Extract File List from Explorer to Excel?

    Hi,

    I'm a complete newbie when it comes to Macros in Excel.

    However, I figured out how to get the macro below to work and am wondering if anyone can tell me how to modify it so that I can also get:

    • date file was created
    • date file was modified
    • author
    • size
    • type
    • title

    Hoping you can help.

    Regards

    Kane



    Quote Originally Posted by Ekim View Post
    The following is a repeat of what I posted at http://www.mrexcel.com/board2/viewto...r=asc&start=10 with some amendments.

    All you have to do is to change the stuff between the double lines, specifically:

    Your path – include the quote marks around the path and the final back slash.
    Filetype – if you want all files put “*”; if you want just Excel files, put “xls”; zipped files – “zip” etc.
    Worksheet reference i.e. where the data will go. Currently set as Sheet2 (if this is OK, then leave as is).
    Startrow this is the row from which your data will start. Currently set as row 2 (if this is OK, then leave as is).

    The macro is set to show all files. Just change the path of where your files are located.
    Code:
    Sub ListFiles2()
    Dim fileList() As String
    Dim fName As String
    Dim fPath As String
    Dim i As Integer
    Dim startrow As Integer
    Dim ws As Worksheet
    Dim filetype  As String
    
    '=======================================================
    fPath = "C:\Temp\"
    filetype = "*"
    Set ws = Worksheets("Sheet2")
    startrow = 2    'starting row for the data
    '========================================================
    
    fName = Dir(fPath & "*." & filetype)
    While fName <> ""
        i = i + 1
        ReDim Preserve fileList(1 To i)
        fileList(i) = fName
        fName = Dir()
    Wend
    If i = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
        
    For i = 1 To UBound(fileList)
        ws.Range("A" & i + startrow).Value = fileList(i)
    Next
    Columns(1).AutoFit
    
    End Sub
    See this line in the macro:
    fName = Dir(fPath & "*." & filetype)

    When the above line incorporates the first two defined variables (in-between the double lines above), it becomes:

    fName = Dir(“C:\Temp\*.*”)

    i.e. pick up all files.

    The next post is the non-macro method.

    HTH

    Mike

  9. #9
    Board Regular
    Join Date
    Oct 2009
    Location
    Houston, Texas
    Posts
    52

    Default Re: Extract File List from Explorer to Excel? Modify VBA?

    I'm also new to macros. How would you modify this to show the entire path, (including folders, etc) on one line in excel. I'd like to use it as an inventory and to link as a hyperlink to various reports, etc.
    Thanks

  10. #10
    New Member
    Join Date
    Nov 2010
    Posts
    1

    Smile Re: Extract File List from Explorer to Excel?

    Or you can simply use command promt. Start > Run > type: cmd
    Go to your folder:
    type "cd C:\MyFolder"
    type "dir"

    copy and paste data:

    Directory of C:\Users\Slawek

    07/11/2010 22:05 .
    07/11/2010 22:05 ..
    07/11/2010 22:06 cdmage
    25/09/2010 13:44 Contacts
    28/11/2010 16:00 Desktop
    28/11/2010 19:30 Documents
    29/11/2010 11:51 Downloads
    25/09/2010 13:44 Favorites
    25/09/2010 13:44 Links
    31/10/2010 18:27 Music
    28/11/2010 19:48 Pictures
    25/09/2010 13:44 Saved Games
    25/09/2010 13:44 Searches
    25/09/2010 13:44 Videos
    07/11/2010 02:14 Virtual Machines
    0 File(s) 0 bytes
    15 Dir(s) 119,936,057,344 bytes free

    simple

Page 1 of 2 12 LastLast

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
  •  


DMCA.com