Extract File List from Explorer to Excel?

1cyril1

Board Regular
Joined
Mar 31, 2003
Messages
89
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!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Ekim said:
See this recent thread for extracting file details from a specified directory:

http://www.mrexcel.com/board2/viewtopic.php?t=85085&postdays=0&postorder=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 :biggrin:
 
Upvote 0
The following is a repeat of what I posted at http://www.mrexcel.com/board2/viewtopic.php?t=85085&postdays=0&postorder=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
 
Upvote 0
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
 
Upvote 0
What can I say? that's just brilliant. Many thanks for such a prompt and comprehensive response. Best Regards, Cyril :biggrin:
 
Upvote 0
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



The following is a repeat of what I posted at http://www.mrexcel.com/board2/viewtopic.php?t=85085&postdays=0&postorder=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
 
Upvote 0
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
 
Upvote 0
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 <DIR> .
07/11/2010 22:05 <DIR> ..
07/11/2010 22:06 <DIR> cdmage
25/09/2010 13:44 <DIR> Contacts
28/11/2010 16:00 <DIR> Desktop
28/11/2010 19:30 <DIR> Documents
29/11/2010 11:51 <DIR> Downloads
25/09/2010 13:44 <DIR> Favorites
25/09/2010 13:44 <DIR> Links
31/10/2010 18:27 <DIR> Music
28/11/2010 19:48 <DIR> Pictures
25/09/2010 13:44 <DIR> Saved Games
25/09/2010 13:44 <DIR> Searches
25/09/2010 13:44 <DIR> Videos
07/11/2010 02:14 <DIR> Virtual Machines
0 File(s) 0 bytes
15 Dir(s) 119,936,057,344 bytes free

simple :)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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