How to get Subfolder names from Parent folder

[MichealDon]

Dear All,

For the function of FSO (File system Object), we can get files name in folder and put the files name list in the Access database.
For the folder, I wish to do the same that. It mean, I want to get the list of subfolder names in the parent folder, but I do not know how to do.
Addition, in the Table_Folder has 2 field "FolderName" and "CreationDate" (similar as Table_File with "Filename" and CreationDate").
and how to get the Creation Date ?

Thanks advance for stop to my topic and support to me.
Best Regards,
MichealDon

 

[Uncle Gizmo]

Try This:-

Sub ListSubFolders()

    Dim fso As Scripting.FileSystemObject
    Dim parentFolder As Scripting.Folder
    Dim subFolder As Scripting.Folder
    Dim db As Database
    Dim rs As Recordset
    
    ' Initialize the FileSystemObject
    Set fso = New Scripting.FileSystemObject
    
    ' Set the parent folder path (replace with your folder path)
    Set parentFolder = fso.GetFolder("C:\YourFolderPath")
    
    ' Connect to the current Access database and the target table
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblFolder", dbOpenDynaset)
    
    ' Loop through each subfolder and add details to the table
    For Each subFolder In parentFolder.Subfolders
        rs.AddNew
        rs("FolderName") = subFolder.Name
        rs("CreationDate") = subFolder.DateCreated
        rs.Update
    Next subFolder
    
    ' Cleanup
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set fso = Nothing

End Sub

 

[The_Doc_Man]

This is a side comment. Uncle G's suggestion attacks the direct problem.

Using the "." as part of a file or folder name is perhaps a questionable strategy only because the "." ALSO has syntax meaning as part of file names. It is the "file type" delimiter. Use a dash if you must have a separator character. Yes, you CAN continue to use the "." - but it is one man's OPINION that it is unwise. It is the same reason that you should not use file names that contain the "/" character. (Or the "\" character either.)

 

[MichealDon]

Try This:-

Sub ListSubFolders()

    Dim fso As Scripting.FileSystemObject
    Dim parentFolder As Scripting.Folder
    Dim subFolder As Scripting.Folder
    Dim db As Database
    Dim rs As Recordset
   
    ' Initialize the FileSystemObject
    Set fso = New Scripting.FileSystemObject
   
    ' Set the parent folder path (replace with your folder path)
    Set parentFolder = fso.GetFolder("C:\YourFolderPath")
   
    ' Connect to the current Access database and the target table
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblFolder", dbOpenDynaset)
   
    ' Loop through each subfolder and add details to the table
    For Each subFolder In parentFolder.Subfolders
        rs.AddNew
        rs("FolderName") = subFolder.Name
        rs("CreationDate") = subFolder.DateCreated
        rs.Update
    Next subFolder
   
    ' Cleanup
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set fso = Nothing

End Sub

Dear Uncle Gizmo

Thank you very much for your help with VBA code. I will update these code in my database and try it.

Best Regards,
MichealDon

 

[MichealDon]

This is a side comment. Uncle G's suggestion attacks the direct problem.

Using the "." as part of a file or folder name is perhaps a questionable strategy only because the "." ALSO has syntax meaning as part of file names. It is the "file type" delimiter. Use a dash if you must have a separator character. Yes, you CAN continue to use the "." - but it is one man's OPINION that it is unwise. It is the same reason that you should not use file names that contain the "/" character. (Or the "\" character either.)

Dear The_ Doc_Man,

Thank you for your comment in the post. With your reply, we will understand more in using the "dot" in our programming.

Best Regards,
MichealDon

 

[MichealDon]

And finally, why would you ever want to import the folder list to to a table? You have no control over this data. It could change tomorrow and your list would be out of sync. It would probably be better to save the folder and file as separate fields and then use the folder column to make your list.

Dear Pat Hartman,

Thank you for advice for more information on special characters and give the way to use them.

For my daily work, everyday my system create hundred of folders automatically. To check the existence of folders in my system, I need to build a Access database to do . That is reason why I need to take the list of folders.

Best Regards,
MichealDon

 

[moke123]

Here's an example that will list the folder name, folder path, created date, last accessed date, and the file count for each folder.

There are 2 separate procedures included and both have an option to do recursion to list the subfolders in each folder.
The first will just add all the folders to the table however you'll get duplicates when run a 2nd time.

The second version will only write the folders to the table if they are not already there. No Duplicates so the data will be in sync.

 

[ebs17]

For my daily work, everyday my system create hundred of folders automatically.

I think a lot of holding new information in my hands instead of throwing it into a lake and looking for it again straight away. If you master your system, you can automatically write a table entry with all the necessary information with a folder creation. Based on the structure, it doesn't seem likely that someone from a third party will interfere with the file system.

how to get the Creation Date ?

For what specific purpose is the creation date required?

FControl ...
I would not name folders and files the same and integrate the validity date right into the name.
=> FdControl_20190119, FlControl_20190120.xxx

With a usable integration of the date in the file name, you can probably do without many directories, the assignment to the month should be clear.

 

[MichealDon]

Here's an example that will list the folder name, folder path, created date, last accessed date, and the file count for each folder.

There are 2 separate procedures included and both have an option to do recursion to list the subfolders in each folder.
The first will just add all the folders to the table however you'll get duplicates when run a 2nd time.

The second version will only write the folders to the table if they are not already there. No Duplicates so the data will be in sync.

Dear ebs17,
Thank you a lot. The attachment is a database that I need to have for my purpose.
I used to do this manually. This mean, I have to do this task through excel file. It spend much more time and incorrect.
Thank again.
MichealDon

 

[MichealDon]

I think a lot of holding new information in my hands instead of throwing it into a lake and looking for it again straight away. If you master your system, you can automatically write a table entry with all the necessary information with a folder creation. Based on the structure, it doesn't seem likely that someone from a third party will interfere with the file system.

Dear ebs17,

Thank you for your reply .
Of course, the system is created by another persons. I am responsible for check the existence of data on backup . Due to many reason such as network connection that can cause some folder will be missing in the backup directory.

For the Creation date. (the time and date of folder was created )
Just only reason to filter the database later if needed.

For the name of file and folder to be mentioned in above. This illustration is only for demo purpose.

Thank you.
MichealDon

 

[ebs17]

This illustration is only for demo purpose

I understand: Modeled to be unrecognizable and possibly distorted.

If "of course" others get involved, the file system must be read in from scratch every time. A very simple, convenient and fast option is also via Excel / Power Query. With a few clicks in the user interface and without special programming knowledge, you get all the necessary information (file name, path, date of accessed/modified/created), the paths can also be split into levels.

You can continue the processing at the same place (almost everyone can do Excel), but of course you can also remotely control it from Access and then process the table data in the database.

 

[Galaxiom]

My last fight on this topic with Access was to import files from a Unix system where they use dots everywhere.

I deal with this every day. It is particularly horrendous in the UniVerse PICK based file and database system. Both the dot and the underscore can appear in object names as well as being separators between table names and field names.

This dual meaning syntax is supported without the use of delimiters around the object names so TABLE.NAME.FIELD.NAME is a valid expression. The underscore is used for names in dynamically normalized structures where multivalued fields are transparently presented as virtual tables so TABLE.NAME_MULTIVALUE_FIELDNAME.FIELD.NAME is valid.

The ODBC connection cannot handle the dot in names so converts them to underscores, appending _1 to the otherwise duplicated names where one expression contained a dot and the other an underscore. The converted name can take precedence while an original name with the underscore gets the _1.

The UniVerse name of the primary key field (always text) is @ID so that is converted to Z_ID in ODBC.

I connect to ODBC through an SQL Server Linked Server and ODBC supports native UniVerse SQL queries while UniVerse SQL supports another language in EVAL expressions called RetrieVe. Hence I can be speaking T-SQL, UniVerseODBC, UniVerseSQL and RetrieVe in a single query on the SQL Server. Does my head in sometimes.

 

[DaveT2001]

Here's an example that will list the folder name, folder path, created date, last accessed date, and the file count for each folder.

There are 2 separate procedures included and both have an option to do recursion to list the subfolders in each folder.
The first will just add all the folders to the table however you'll get duplicates when run a 2nd time.

The second version will only write the folders to the table if they are not already there. No Duplicates so the data will be in sync.

Nice piece of work