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.)

 

[Pat Hartman]

Add to that one woman's opinion Chances are excellent that there won't be a counter argument on that point either; at least not from someone who works in the Windows world. It's been a lot of years since MS modified windows to expand file names from their original 8.3 format and then to also allow embedded spaces and special characters. But I still occasionally run into software that didn't get the memo and I can count certain parts of Access that fail when offending characters are used in file names. I NEVER use "dots" in file or folder names. In the Windows world, the "dot" is used to separate the file name from the file type. I also never use spaces or special characters except for the underscore. Any deviation and you too might run afoul of some old code in Access (or some other application) that objects to your file names. My last fight on this topic with Access was to import files from a Unix system where they use dots everywhere. Some part of the import process failed. I can't remember now whether it was the import spec or the TransferText itself. In any event, the solution was to rename the file to remove the offending dots from the filename prior to importing it. At least FSO didn't break when reading the bad file names.

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.

 

[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.

 

[Pat Hartman]

Wow! That's a blast from the past. While I never developed using Pick, One of the teams I managed did. The other two teams were working on the conversion from Pick to Oracle and Oracle forms which I had never developed in either. The company had two physical offices 25 miles apart so I had two offices. My Pick team was housed in one location and the other two were in the other so I split my time. Each office had tHREE, yep, count em', THREE computers. A Windows PC, a Mac, and an X-terminal (the oracle forms team) The only reason for the X-Terminal was because the Mac purists flatly refused to attach two button mice to the Macs and the x-terminal emulator needed the two-button mouse. So the Mac purists won and I ended up with two extra x-terminals. Talk about idiocy. The place was totally insane with the three technical groups at odds at all times.

 

[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