Getting a List of Folder Contents

Never mind; I got it to work!

I just can't get this to work; when I copy the file path and paste it, this is what I get. "W:\WORKGROUPS\P&E"
Not sure what I am doing wrong!

Here's the way I do it in a macro to create a folder listing in a document via piping the DIR command to a temporary file. This allows using all of the DIR command's options. This is the guts of a larger procedure I use to compare the details of the files (dates, sizes, etc.) in two different directories using Word's CompareDocs feature.

Sub CreateFolderListing()
Const Switches As String = "/OGN /TW /B" ' Add "/S" for subfolders
Dim CommandLine As String
Dim FolderName As String
Dim TempName As String
Dim FSO As New FileSystemObject

FolderName = "c:\"
TempName = FSO.GetSpecialFolder(TemporaryFolder) & "\" & FSO.GetTempName
CommandLine = "cmd.exe /C dir " & Switches & " """ & FolderName & """ > """ & TempName & """"
Call CreateObject("WScript.Shell").Run(CommandLine, 2, True) ' See https://ss64.com/vb/shell.html
Documents.Add.Content.InsertFile filename:=TempName, ConfirmConversions:=False
Kill TempName
End Sub

Getting a List of Folder Contents.
I followed the directions for this task and it worked first time. Great. I have wanted to do that a few times and never knew how.

I want to get macros working in Excel but have had no luck. At one time I was fluent with macros using the ancient Apple II Appleworks program. It was fantastic.

I would also like a VERY SIMPLE flat file database that is popular enough for other people to read. I wish Microsoft had made such a program.

I LOVE THIS TIP! SO EASY! I had a folder of documents for an instructor that he wanted help uploading to his course. After a week, he had not provided what dates to make items available. I used this process and emailed the document to him. He replied in 20 minutes!!! T

Thank you for such a helpful tip!!!

A fast and easy way is to open the folder and using a snipping tool make a copy. This will not give you list you can manipulate, but you can print it and have access to a list of your files on paper.

I recommend this third party tool: Directory Report
It can save the contents of your directories to many file formats: txt, csv, xml, directly to MS-Excel, and clipboard
it can get the attributes of: DLL/Exe, AVI, MP3, MSI, WAV and MS-Office properties

Hi Allen
I use Directory Report to get a List of Folder Contents
It can export a directory list directly to MS-Excel
Very customizable

Ken & Paul:

That's a lot of files . . . if I had the need to post a list even much smaller than that and potentially sort on it, I would be inclined to create the list in Excel.

To do that, I would engage a very powerful freebie called ASAP Utilities that has a built in utility to create such a file list (including subfolders) in an Excel file.

(Happily I don't have a need to reference that quantity of files in a Word document).

I was going to give a warning not to try the macro on very large folders such as C:\Windows or C: but Paul has beaten me to it.

For file properties that can be listed, see:
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/file-object

DateLastModified is probably more useful than DateCreated in the macro, or maybe you need both.

FYI, if you have 58473 files spread across 14354 sub-directories, Ken's macro will give you a Word document with all the files & sub-folders, but it won't create the table, due to Word's limit. Totally neat trick though!

Oh My Goodness! This is a FABULOUS Tip!!! Thank you so very much. You just saved me bushels of time!

The solution shown will give a list of files in a folder but not its sub folders.

The following macro will produce a list of all files in a folder and its sub-folders. The detail given for each file is the path, the name, the size and the creation date. Other file information could be shown by changing the statement flNames(UBound(flNames)) = …

The results are put into a table in a new document. Word’s Sort can then be used to sort into any order.

' ***** This declaration must be at the top of the module *****
Dim flNames() As String
' ************************************************************
Sub FilesInFolderAndSubFolders()
Dim FileSystem As Object
Dim mydir As String
Dim j As Long
mydir = InputBox("Folder path. For example C:\MyFolder")
ReDim flNames(0)
Set FileSystem = CreateObject("Scripting.FileSystemObject")
DoFolder FileSystem.GetFolder(mydir)
Documents.Add
Selection.TypeText "Path" & vbTab & "File name" & vbTab & _
"Size" & vbTab & "Creation Date" & vbCrLf
For j = 1 To UBound(flNames)
Selection.TypeText flNames(j)
Next j
ActiveDocument.Range.Select
Selection.ConvertToTable Separator:=wdSeparateByTabs, AutoFitBehavior:=wdAutoFitFixed
Selection.Tables(1).Style = "Table Grid"
End Sub

Sub DoFolder(Folder)
Dim SubFolder
Dim aFile
Dim nextDoc As Document
For Each SubFolder In Folder.SubFolders
DoFolder SubFolder
Next SubFolder
For Each aFile In Folder.Files
ReDim Preserve flNames(UBound(flNames) + 1)
flNames(UBound(flNames)) = Folder & vbTab & _
aFile.Name & vbTab & aFile.Size & vbTab & aFile.DateCreated & vbCrLf
Next aFile
End Sub

I too frequently need to list the file contents of a folder in my reports.

I discovered it is fastest to open the target folder in Explorer, organize/display the files you want (e.g. either or without attributes), simply take a screen shot of the file list and paste the graphic into your document.

If the directory path is important to you, just expand the screen shot coverage to include the path shown in the navigation bar too.

"Copy as Path" does not show up on my Windows 10 (latest version) context menu. Any help there?