Extract File List from Explorer to Excel?

[1cyril1]

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!

 

[kailey47]

Hey I know this post was from a while ago but I have found it very helpful. I need some more help though.... I am using the macro that Ekim posted originally.

I would like to include subfolders, so I want every file found in C:\Movies and it's subfolders. If I could have a 2nd column included with the files 1 up folder that would be great, but if it's very tricky I will leave it as I am still new to Macro's.

I am using Excel 2010, incase that matters.... Any help would be much appreciated.

Cheers
Kailey

 

[dVBA]

The code snippet in this link lists files in a subfolder... can be modified to suit needs

http://dynamicvba.com/index.php?opt...les-in-a-directory&catid=4:basicvba&Itemid=16

 

[kailey47]

OK I am trying to use that code, but it is coming up with tonnes of errors saying the code is invalid, things don't exist etc.

Is there any way of editing the existing code to do what I need?

 

[dVBA]

I'm sorry I went to sleep shortly after I posted last night... we can make this work for you... can you post the altered code you used and I can check it, alternatively you can PM me and I will go over everything that would need changed to work on your personal computer/network directory.

 

[kailey47]

Ok this is the code I used. The error I get is for the line I've made red. It is

Compile Error: Method or data member not found

Option Explicit

Public Sub GetFiles()
ListFiles "G:\MUSIC\"
End Sub


Private Sub ListFiles(ParentDir As String)
Dim Folder As Object
Dim Subfolder As Object
Dim File As Object
Dim FileSysObj As Object

Set FileSysObj = CreateObject("Scripting.FileSystemObject")
Set Folder = FileSysObj.GetFolder(ParentDir)

ThisWorkbook.Sheet("Sheet1").Range("A1:A50000").ClearContents

For Each Subfolder In Folder.Subfolders

For Each File In Subfolder.Files

If InStr(LCase$(File.Name), "*") > 0 Then _
ThisWorkbook.Sheets("Sheet1").Range("A50000").End(xlUp).Offset(1, 0).Value = _
Right(File.Path, Len(File.Path) - Len(ParentDir))

Next File

Next Subfolder

Set FileSysObj = Nothing
Set Folder = Nothing

End Sub

I have played around a bit, but I really have no idea what I am doing... any help would be greatly appreciated.

Cheers
Kailey

 

[dVBA]

Change the line in red to the one below... let me know if that fixes it

ThisWorkbook.Sheets("Sheet1").Range("A1:A50000").ClearContents

www.dynamicvba.com

 

[Sanjeev1976]

Check the below link which gives a complete directory list :

http://www.cpearson.com/Excel/FolderTree.aspx

I believe it will resolve your query effectively.

 

[jameschen768]

hi sorry to bug ya, but how do i set the column where i want it to start pasting? i tried startCol to avail, so i've added a piece on the bottom to cut and paste it to where i want to be. so i guess i'm just asking to be curious.

 

[Maureen5433]

I am looking at this method but am now using Outlook 2007. I don't see the "Other Shortcuts" option - anyone know how to accomplish this using this method or another non macro method?

I have a lot to do but my macro skills are shaky at best.

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

 

[BenElliott]

Just come across this thread as I am trying to extract a list of "xls" files into Excel 2010. I've tried the VB code listed on page 1 of this thread, altering the path within the lines indicated. But, despite pinting to a folder with files in it the routine returns "no files found". Am I doing something wrong?