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!!
Eileen MurphyIndependent Application DeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor 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
 
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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
 
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
 
Eileen MurphyIndependent Application DeveloperAuthor Commented:
Awesome! Thanks for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.