Amazing, I surfed around to find a simple utility that could list files recursively from a given top folder down through it, writing out the path and filename in simple \path\file_name form, without writing out empty folder names, if any. Well I found a couple utilities but they all tried to do all this other stuff, do it in Excel with no copy and paste, etc. Not a one that just writes a simple text file with the path and file name. So I finally surrendered to the idea that I would have to hack up a solution myself.
Do as follows:
1. In an Access .mdb file, create a new form.
2. Add these elements:
textbox:
Name: txtBasePath
Label: "Base Path:"
checkbox:
Name: chkIncludeBase
Label: "Include Base Path"
command button:
Name: cmdMakeList
Caption: "Make List"
Label (unassociated):
Name: whatever you feel like
Caption: "Look for list at C:\filelist.txt"
3. Save form.
4. Open form's code module
4a. Select Tools...References and make sure the 'Microsoft Scripting Runtime' library entry is checked.
5. Copy and paste the following into the form's code module:
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''
- Option Compare Database
-
-
Private cnt As Long
-
-
Private Sub cmdMakeList_Click()
-
-
On Error GoTo errorh
-
-
txtBasePath.SetFocus
-
If IsNull(txtBasePath.Text) Then
-
Exit Sub
-
End If
-
-
If txtBasePath.Text = "" Then
-
Exit Sub
-
End If
-
-
Dim fso As New FileSystemObject
-
Dim ts As TextStream
-
Dim fldr As Scripting.Folder
-
Dim basepath$
-
-
basepath = txtBasePath
-
cmdMakeList.SetFocus
-
-
Set ts = fso.CreateTextFile("C:\filelist.txt", True, False)
-
Set fldr = fso.GetFolder(basepath)
-
writeFiles fldr, ts
-
-
ts.Close
-
-
MsgBox "Wrote " & cnt & " lines.", vbInformation, "Done"
-
cnt = 0
-
Exit Sub
-
-
errorh:
-
-
cnt = 0
-
-
MsgBox "Error occurred.", vbInformation, "Error occurred"
-
MsgBox Err.Number & ": " & Err.Description
-
-
On Error Resume Next
-
ts.Close
-
-
End Sub
-
-
Public Sub writeFiles(myfldr As Scripting.Folder, ts As TextStream)
-
-
Dim basepath$, name$
-
Dim fil As Scripting.File
-
Dim fils As Scripting.Files
-
Dim fldr2 As Scripting.Folder
-
Dim fldrs As Scripting.Folders
-
-
basepath = txtBasePath
-
-
Set fils = myfldr.Files
-
Set fldrs = myfldr.SubFolders
-
-
For Each fil In fils
-
If fil.Attributes <> Directory Then
-
name = fil.path
-
If chkIncludeBase = 0 Then
-
name = Right(name, (Len(name) - Len(basepath)))
-
End If
-
ts.WriteLine name
-
cnt = cnt + 1
-
End If
-
Next
-
-
For Each fldr2 In fldrs
-
writeFiles fldr2, ts
-
Next
-
-
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''
6. Save code.
7. Run form.
Find the folder from which you want to get the list, copy its path, add it to the textbox, then click the button. Voila, easy as cake.
If you find any boo-boos, please post. Otherwise you are free to use it, adapt it, add more error handling, add a folder-picker, whatever. The annoying part however is done for you. Enjoy!
