• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

VBA Code to populate a table with a list of FOLDERS in a specified drive

Hello Experts.

I have the code to populate a table with the files in a specified location but can't seem to get the FOLDER NAMES and store the results in a table.

Any help would be appreciated.

Thanks!!
0
Eileen Murphy
Asked:
Eileen Murphy
  • 5
  • 5
1 Solution
 
Rey Obrero (Capricorn1)Commented:
<I have the code to populate a table with the files in a specified location>

post the codes you are using...


or see this link

http://allenbrowne.com/ser-59.html
0
 
Eileen MurphyIndependent Application DeveloperAuthor Commented:
That's the code I use to get the filenames in a specified folder -- works great! I now want to get the folder names and can't get it to work...
0
 
Rey Obrero (Capricorn1)Commented:
ok.. in this part of the code


 'Add the files to the folder.
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> vbNullString
         gCount = gCount + 1
         SysCmd acSysCmdSetStatus, gCount
        strSQL = "INSERT INTO Files " _
          & " (FName, FPath) " _
          & " SELECT """ & strTemp & """" _
          & ", """ & strFolder & """;"

         CurrentDb.Execute strSQL
        colDirList.Add strFolder & strTemp
        strTemp = Dir
    Loop


' strFolder is the string that holds the complete folder path

and the code is storing it in the field FPath..
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Eileen MurphyIndependent Application DeveloperAuthor Commented:
When I run the code it returns the path ONLY for files found in the folder.... what I want is to populate the table with all the folders within a specific folder or network path or whatever.
0
 
Rey Obrero (Capricorn1)Commented:
are you using the exact code i posted?
0
 
Eileen MurphyIndependent Application DeveloperAuthor Commented:
I am. When I run it against a folder with 58 subfolders and 8 files I get 8 records in my table.
0
 
Rey Obrero (Capricorn1)Commented:
I get 8 records in my table with the folder path ? is this correct

and are you expecting to get all the 58 subfolders , 50 of which does not have file on them ?
0
 
Eileen MurphyIndependent Application DeveloperAuthor Commented:
Right -- I want to populate a table with the directory structure (folder names) basically.. they represent their client list as each folder is unique to their customers...
0
 
Rey Obrero (Capricorn1)Commented:
try this revision of the codes above


    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> vbNullString
      
            if strTemp & ""="" then
                  strSQL = "INSERT INTO Files " _
                    & " (FName, FPath) " _
                    & " SELECT """ & strTemp & """" _
                    & ", """ & strFolder & """;"
                   CurrentDb.Execute strSQL
              end if
      
      
      
         gCount = gCount + 1
         SysCmd acSysCmdSetStatus, gCount
        strSQL = "INSERT INTO Files " _
          & " (FName, FPath) " _
          & " SELECT """ & strTemp & """" _
          & ", """ & strFolder & """;"
         CurrentDb.Execute strSQL
        colDirList.Add strFolder & strTemp
        strTemp = Dir
    Loop
0
 
Eileen MurphyIndependent Application DeveloperAuthor Commented:
Awesome! Thanks for your help!
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now