Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Wanting a list of directories (MSACCESS 2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wanting a list of directories (MSACCESS 2003)

    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

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wanting a list of directories (MSACCESS 2003)

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

  4. #4
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wanting a list of directories (MSACCESS 2003)

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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wanting a list of directories (MSACCESS 2003)

    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.

  6. #6
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wanting a list of directories (MSACCESS 2003)

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

  7. #7
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wanting a list of directories (MSACCESS 2003)

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wanting a list of directories (MSACCESS 2003)

    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.

  9. #9
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wanting a list of directories (MSACCESS 2003)

    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.
    Attached Images Attached Images

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wanting a list of directories (MSACCESS 2003)

    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?

  11. #11
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wanting a list of directories (MSACCESS 2003)

    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

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wanting a list of directories (MSACCESS 2003)

    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

  13. #13
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wanting a list of directories (MSACCESS 2003)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •