Wanting a list of directories (MSACCESS 2003)

[ZOR]

I am trying to do 2 things. The first is to fill a list with directory names within a main directory. Then I want to get word document names within those directories, again in a list.

The first part I am trying seems to only produce documents, no directory names.

Dim sPath As String
Dim sFile As String
sPath = "C:"
sFile = Dir(sPath, vbDirectory)
Me.List2.RowSourceType = "Value List"
Do While sFile <> ""

Me.List2.AddItem Item:=sFile

sFile = Dir
Loop

Many thanks

[HansV]

You can't use Dir for that. Try this instead:

Dim fso As Object
Dim fld As Object
Dim sfl As Object
Dim fil As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder("C:")
' Loop through the subfolders
For Each sfl In fld.SubFolders
Me.List2.AddItem sfl.Name
' Optional - loop through the files in the subfolder
For Each fil In sfl.Files
' Do something, e.g.
Debug.Print fil.Name
Next fil
Next sfl

[ZOR]

Thanks Hans, that was exactly what I was trying to do. Many thanks again regards

[ZOR]

Is there a way to do what this code does, but drill down into further subdirectories of subdirectories if they exist. Thanks

[HansV]

You can use the following recursive code as starting point:

Sub ListFilesAndSubFolders(fld As Object)
Dim fil As Object
Dim sfl As Object
For Each fil In fld.Files
Debug.Print fil.Name
Next fil
For Each sfl In fld.SubFolders
Debug.Print sfl.Name
Call ListFilesAndSubFolders(sfl)
Next sfl
End Sub

Sub ParseFolder(FolderName As String)
Dim fso As Object
Dim fld As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(FolderName)
Call ListFilesAndSubFolders(fld)
End Sub

Replace Debug.Print ... with the action you want.

Use like this:

ParseFolder "Cocuments and SettingsZORMy Documents"

Warning: if you try to parse a folder with many levels of subfolders, Windows may run out of resources.

[ZOR]

Thank you very much Hans, was able to use it perfectly. Very best regards, thanks again.

[ZOR]

I think I am going the long way round this, not fully understanding the processes so I may have some unwanted extra code in it. I am trying to create a process of sequentially (so I can make sense of it to fill tables) going through directories and subdirectories logging reference to their names, and the file contents within them. (They are all word document names)

So far what I have works as far as naming the directory and finding all the documents within the directory. I can also get names of subdirectories listed, but I have ground to a halt as to how I list the documents within the related sundirectories. Would be god to see the light at the end of the tunnel. Thanks

Sub ListFilesAndSubFolders(fld As Object)
' PART ONE CODE VERSION ONLY THIS GETS A LOOP OF MAIN DIRECTORIES
Dim fil As Object
Dim sfl As Object

Me.List3c.RowSource = ""
Me.List3.RowSource = ""

For Each sfl In fld.SubFolders
' THESE ARE THE DOCUMENTS IN THE INDIVIDUAL MAIN FOLDERS
Me.List3.AddItem UCase(sfl.Name)

' During this loop need to get loop of documents within main folders
ParseFolder2 "kocuments" & "" & sfl.Name

' During this loop need to get loop of subdirectories within main folders
ParseFolder3 "kocuments" & "" & sfl.Name

Next sfl


' For Each sfl In fld.SubFolders
' ParseFolder3 "kocuments" & "" & sfl.Name
' Next sfl


End Sub
Sub ListFilesAndSubFolders2(fld As Object)
Dim fil As Object
Dim sfl As Object
' THESE ARE THE DOCUMENTS IN THE INDIVIDUAL MAIN FOLDERS
For Each fil In fld.Files
Me.List3c.AddItem fil.Name
Next fil
End Sub
Sub ListFilesAndSubFolders3(fld As Object)
Dim fil As Object
Dim sfl As Object
' THESE ARE THE SUBDIRECTORY NAMES UNDER INDIVIDUAL MAIN FOLDERS
For Each sfl In fld.SubFolders
Me.List3b.AddItem sfl.Name
Next sfl
End Sub
Sub ParseFolder(FolderName As String)
Dim fso As Object
Dim fld As Object
' THESE ARE MAIN DIRECTORY NAMES
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(FolderName)
Call ListFilesAndSubFolders(fld)
End Sub
Sub ParseFolder2(FolderName2 As String)
Dim fso As Object
Dim fld As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(FolderName2)
Call ListFilesAndSubFolders2(fld)
End Sub
Sub ParseFolder3(FolderName3 As String)
Dim fso As Object
Dim fld As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(FolderName3)
Call ListFilesAndSubFolders3(fld)
End Sub

[HansV]

Try this variation on my earlier reply:

Sub ListFilesAndSubFolders(fld As Object)
Dim fil As Object
Dim sfl As Object
For Each fil In fld.Files
Me.List3c.AddItem fil.Name
Next fil
For Each sfl In fld.SubFolders
Me.List3b.AddItem sfl.Name
Call ListFilesAndSubFolders(sfl)
Next sfl
End Sub

Sub ParseFolder(FolderName As String)
Dim fso As Object
Dim fld As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(FolderName)
Me.List3b.RowSource = ""
Me.List3c.RowSource = ""
Call ListFilesAndSubFolders(fld)
End Sub

Note that List3 isn't used. You have one list box with files, another one with folders, no need for a third one. The code is called like this:

ParseFolder "C:Sports"

where C:Sports is the folder you want to process.

[ZOR]

Thanks Hans. I did in fact try your earlier version, and like this produces what is wanted, however I am trying to split the procedure into blocks which permit me to save the related directory/documents into tables and remove the use of listboxes. I tried tracing it in dubug but it did not seem easy to relate them for insertion to a table system. I am trying to fill 4 tables,

1) Main Directory
2) Documents within the Main Directory
3) Subdirectory
4) Documents within subdirectory

A relationship of these is attached.

Many thanks.

[HansV]

Is there a specific reason for wanting to keep the files in the "main" folder separate from the files in the subfolders and their subfolders etc.? Wouldn't it be easier to use one table for folders and subfolders, and another table for files?

[ZOR]

Thanks Hans. Yes I know it looks a silly seperation, but unfortunately that is how the user files documents. They say they have a good reason for it, but I've yet to hear it. They are dictating it stays like that, giving me the headache. Regards

[HansV]

How about this?

Sub ListFilesAndSubFolders(fld As Object, Optional blnMain As Boolean)
Dim fil As Object
Dim sfl As Object
Dim ctl As ListBox
If blnMain Then
Set ctl = Me.List3
Else
Set ctl = Me.List3c
End If
For Each fil In fld.Files
ctl.AddItem fil.Name
Next fil
For Each sfl In fld.SubFolders
Me.List3b.AddItem sfl.Name
Call ListFilesAndSubFolders(sfl)
Next sfl
End Sub

Sub ParseFolder(FolderName As String)
Dim fso As Object
Dim fld As Object
Me.List3.RowSource = ""
Me.List3b.RowSource = ""
Me.List3c.RowSource = ""
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(FolderName)
Call ListFilesAndSubFolders(fld, True)
End Sub

[ZOR]

Many thanks Hans, sorry for the delay getting back, as I traced it in debug and found my breakpoints for getting the data into my tables. Looks like you have finally solved my problems, very greatful for your help. Very best regards, thanks again.