Solved Multiple search engine with dropdowns (1 Viewer)

[zelarra821]

Yes the filesytemobject has attributes. I will add the code.

Ok

I showed the Local folders and files and the total. Mainly for error checking. You can display whatever you want.
Folder 6/34 means 6 folders directly within the given folder and 32 total subfolders.

Ok

I do not understand

This

It is a tree view. Each node has text. So you cannot add coumns. I do not know what that would look like.

Simple curiosity, because I don't know how it is built

 

[zelarra821]

And another thing, because I'm seeing how to use it.

I had thought of putting an option box in the form where the tree is with the two paths with which I am going to use it plus a third in case I wanted to analyze a specific directory; set the view to JPK Ms Forms Based TreeView, because the other does not work;
and integrate all this database in which I am using, would it update every time I start the form?

 

[zelarra821]

And let's see if you can tell me what to remove so as not to leave a trace in the other system's database (active x treeview MS common controls)

 

[zelarra821]

I'll tell you a bit about my idea. My idea is to stick with the continuous form we were editing before this tree, and use this as a control and query, because it gives me a correct file count (once you remove the hidden and system files). In windows explorer, if you try to see the files in a folder, it may give you an "incorrect" value because it counts hidden and system files. What you would need would be a field with the total of files (sum).

What I am going to remove is also the table view subform under the tree. I have that information above, and I don't see much use for it.

The controls that I have indicated in the capture, well look, if they work, well, there they are, and if not, then I don't care.

I just saw that there is a button to read the files, which would be like a button to update and show the new changes in the folder.

The button to clear the table I do not know what to do. It gives me that it is a little irrelevant, because having a button to update and another button, or option to change the route (now I explain it), it does not make sense to have that button there.

As for changing the path, I will use this tree to display two different folders. So, seeing a bit how it is assembled, although for me the code is quite complex and difficult to understand, since I do not get to that much, I think the best thing will be to change the button to change the default folder for an options box where I can choose between three options: one, folder1; two, folder 2; and 3, which opens the dialog you have made to choose a different route.

What I would like is to leave the necessary code for what I am going to use in the database. As I said before, I want to remove Active x because it doesn't work for me. I think tables and queries are the ones they have to be. And forms would have to remove frmActiveX_Tree, frmChoose, subFrmFileFolderCounts, and subTreeView, which I don't know what it's for. As for macros and modules, I don't know what is left over.

Then in VBA there is an empty form, which I don't know if it can be removed.

 

[MajP]

1. Keep in mind this was not meant to be a solution to your problem it was a meant to demonstrate all the code you need and how it works. So use the pieces, but keep a copy so you can reference what it does. It was meant to demonstrate how you get the folders and counts which was your original request. The treeview was to help you visualize the results. So add or remove what you want. There is a lot of extra stuff just for demonstration purposes

2.

What I am going to remove is also the table view subform under the tree. I have that information above, and I don't see much use for it.

You need to look at both the filefolder and foldersummation and then determine what data you really want to read and save. The details (tblFoldersFiles) runs in about 10-15 seconds for 1220 records, but will get slower with more data. Just the counts runs in a few seconds. That subform and query are to demonstrate a self referencing join. Many people are not familiar whith a table that has a foreign key to its self. This is how you save hierarchical data.

3.

The controls that I have indicated in the capture, well look, if they work, well, there they are, and if not, then I don't care.

. The first example uses an Active X tree view control. The second example was made using only form controls. It uses a UserForm, the same type of form you use in Excel. It build the form dynamically by adding controls like textboxes, checkboxes and lines. The logic is very complex. Imagine trying to draw that treeview in Access using only lines and textboxes. In Excel you can add controls to a form at runtime. In access that is difficult to do and causes a lot of problems. That control is an MS Form with a container inside of it. You cannot get rid of it, because that is what makes the treeview.

4.

I just saw that there is a button to read the files, which would be like a button to update and show the new changes in the folder.
The button to clear the table I do not know what to do. It gives me that it is a little irrelevant, because having a button to update and another button, or option to change the route (now I explain it), it does not make sense to have that button there.

The clear button was to demonstrate that you could empty the table and clear the tree view in preparation to load a new directory. Then read the directory and insert the files into the table.

5.

As for changing the path, I will use this tree to display two different folders. So, seeing a bit how it is assembled, although for me the code is quite complex and difficult to understand, since I do not get to that much, I think the best thing will be to change the button to change the default folder for an options box where I can choose between three options: one, folder1; two, folder 2; and 3, which opens the dialog you have made to choose a different route.

Understand there are two different things. 1. Reading and storing the directory, and 2 loading the tree. Once you read the data loading the tree is pretty fast. You could read multiple folders of data and have one tree view with buttons to show the different folders. You just load the tree with the specific starting directory. Or you could do what I did and have many different trees on different tabs.
If you are going to want to "span" more than one folder the data can actually all go into one table with a little modification. In fact you could store as many "trees" as you want in one table. The reason is all records have a parent. If you know the top record ID you can then make the tree.

6.

I'll tell you a bit about my idea. My idea is to stick with the continuous form we were editing before this tree, and use this as a control and query, because it gives me a correct file count (once you remove the hidden and system files). In windows explorer, if you try to see the files in a folder, it may give you an "incorrect" value because it counts hidden and system files. What you would need would be a field with the total of files (sum).

As I said you do not need the Tree at all it was just to demonstrate how to display hierarchical data (a directory tree). You may not even want the details which stores the files and their path. The tblSummation really has everything you originally asked for. It has every folder and the folder/file counts. I store both the total folders and files and the immediate (local) folder and files as counts. Provide more details on this. What folders would you load and store?

7.

What I would like is to leave the necessary code for what I am going to use in the database. As I said before, I want to remove Active x because it doesn't work for me. I think tables and queries are the ones they have to be. And forms would have to remove frmActiveX_Tree, frmChoose, subFrmFileFolderCounts, and subTreeView, which I don't know what it's for. As for macros and modules, I don't know what is left over. Then in VBA there is an empty form, which I don't know if it can be removed.

You can remove all of thoses except subTreeView which is the MS Forms Treeview. If you remove the Active X treeview you can also remove class module TreeviewForm. That is for the ActiveX

 

[zelarra821]

Good Morning. Thank you very much for the information.

Could you add what I told you about the hidden and system files, so that they are not shown?

Also see if you could add a field with the total file count.

Thank you!

 

[MajP]

Use this code.

Private Sub SpanFolders(SourceFolderName As String, Optional ByVal FolderLevel = 0)

' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
    'Dim FSO As Object 'Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder 'Scripting.Folder
    Dim SubFolder As Scripting.Folder 'Scripting.Folder
    Dim FileItem As Scripting.file 'Scripting.File
    Dim ParentID As Long
 
    FolderLevel = FolderLevel + 1
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
       ParentID = GetParentID(SourceFolder.Name)
       For Each SubFolder In SourceFolder.SubFolders
       LogFilesFolders SubFolder.Name, SubFolder.Path, SubFolder.Type, ParentID, fft_Folder, FolderLevel
       SpanFolders SubFolder.Path, FolderLevel
    Next SubFolder

    For Each FileItem In SourceFolder.Files
       If (FileItem.Attributes And 2) <> 2 And (FileItem.Attributes And 4) <> 4 Then
         LogFilesFolders FileItem.Name, FileItem.Path, FileItem.Type, ParentID, fft_File, FolderLevel
       End If
    Next FileItem
    Set FileItem = Nothing
    Set SourceFolder = Nothing

End Sub
Private Sub SpanFolderSummations(SourceFolderName As String, Optional ByVal FolderLevel = 0, Optional CountHiddenSystem As Boolean = False)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
    'Dim FSO As Object 'Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder 'Scripting.Folder
    Dim SubFolder As Scripting.Folder 'Scripting.Folder
    Dim FileItem As Scripting.file 'Scripting.File
    Dim ParentID As Long
    Dim FolderCount As Long
    Dim FileCount As Long
   ' Dim TotalFolderCount As Long
   ' Dim TotalFileCount As Long
    
    FolderLevel = FolderLevel + 1
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
        
    For Each SubFolder In SourceFolder.SubFolders
       ParentID = GetCurrentID(SourceFolder.Name)
       FolderCount = SubFolder.SubFolders.Count
       If CountHiddenSystem Then
         FileCount = SubFolder.Files.Count   'This will count hidden as well
       Else
        FileCount = 0
        For Each FileItem In SubFolder.Files
           If (FileItem.Attributes And 2) <> 2 And (FileItem.Attributes And 4) <> 4 Then
             FileCount = FileCount + 1
           End If
        Next FileItem
       End If
       LogFolderSummations SubFolder.Name, SubFolder.Path, FolderCount, FileCount, ParentID, FolderLevel

       SpanFolderSummations SubFolder.Path, FolderLevel
    Next SubFolder
    Set FileItem = Nothing
    Set SourceFolder = Nothing

End Sub

Notice the if check

 If (FileItem.Attributes And 2) <> 2 And (FileItem.Attributes And 4) <> 4 Then
             FileCount = FileCount + 1
           End If

This may seem strange

(FileItem.Attributes And 2) <> 2 And (FileItem.Attributes And 4) <> 4

This is actually done often in VBA (the message box buttons are an example.)

MsgBox function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

It is bitwise math. I will explain because I had to learn how this works.

The attributes are the following.

Normal	0	Normal file. No attributes are set.
ReadOnly	1	Read-only file. Attribute is read/write.
Hidden	2	Hidden file. Attribute is read/write.
System	4	System file. Attribute is read/write.
Volume	8	Disk drive volume label. Attribute is read-only.
Directory	16	Folder or directory. Attribute is read-only.
Archive	32	File has changed since last backup. Attribute is read/write.
Alias	1024	Link or shortcut. Attribute is read-only.
Compressed	2048	Compressed file. Attribute is read-only.

So for example if it is Hidden, System, and Archive the value is (2+4+32) = 38. That is the attribute property
In binary 38 = 100110
2 = 000010
4 = 000100
36 = 100100
In bitwise you compare each bit. (0 and ) = 0)(1 and 0 = 0)(1 and 1) = 1
so (38 and 2) = (10110) and (000010) = 000010 = 2
(38 and 4) = (100110) and (0000100) = 000100 = 4
(38 and 36) = (100110) and (100100) = 100100 = 32

So with one value 38 vba represents three properties

 

[zelarra821]

Good grief, what a paranoia. Yes, I saw that box of the options, and I had no idea how to put several.

I have tested the code and it works.

And another question. If I want to get the sum of all the files?

Thank you!

 

[MajP]

I purposely did not include the very top level folder, but maybe I should. For example the top level folder is DatabaseFiles, but I start at the child folders Descargado and Reproducido. I can change it to begin the log at the top folder. Do you want to include the very top folder? That may make more sense if there are also files at the top level. My thought would be no.

If not you can do an aggregate query on the table.

SELECT Sum(tblFolderSummations.LocalFolderCount) AS SumOfLocalFolderCount, Sum(tblFolderSummations.LocalFileCount) AS SumOfLocalFileCount
FROM tblFolderSummations;

 

[zelarra821]

Well, there you have caught me. I would know how to make a normal query, but that of an "aggregate query on the table", no idea. Where do I add it? Do I have to do it by VBA? Because I guess what you say is that it appears in the subform, but I don't know how to do it.

 

[MajP]

SQL GROUP BY Statement

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[zelarra821]

I thought you were telling me to include it in the subform under the tree. It was just creating a query. It is done. Solved.

What I was missing is that it does not add the level 0 folders to that sum. So that will have to do a union query to obtain the final value.

Thanks a lot.

 

[MajP]

You could do a union, but I think I will change the code so it includes level 0. I think that makes more sense.

 

[zelarra821]

If you do and it appears, the better

 

[MajP]

You only need to import the module "mdlSpanFoldersAndFiles". That is what changed. This is also easy to decode because the folder/file span and the summation span are now basically identical.

 

[zelarra821]

It does not go well. Check out how it worked out before:



And how does it come out now:



The folders are not well structured (compare one image with the other, taking into account that the first is correctly structured); the files that are in ZZZ Videos, now go out; and the file count cannot be 17, when they are more than 1000.

 

[MajP]

Update the module

Private Sub SpanFolders(SourceFolderFullName As String, Optional ParentID As Long = 0, Optional ByVal FolderLevel = 0)

' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
    'Dim FSO As Object 'Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder 'Scripting.Folder
    Dim SubFolder As Scripting.Folder 'Scripting.Folder
    Dim FileItem As Scripting.file 'Scripting.File
   ' Dim ParentID As Long

    
    Set SourceFolder = FSO.GetFolder(SourceFolderFullName)
    
    FolderLevel = FolderLevel + 1
    LogFilesFolders SourceFolder.Name, SourceFolder.Path, SourceFolder.Type, ParentID, fft_Folder, FolderLevel
    ParentID = GetFolderID(SourceFolder.Path)
    For Each FileItem In SourceFolder.Files
        If (FileItem.Attributes And 2) <> 2 And (FileItem.Attributes And 4) <> 4 Then
           LogFilesFolders FileItem.Name, FileItem.Path, FileItem.Type, ParentID, fft_File, FolderLevel
       End If
    Next FileItem
    
    For Each SubFolder In SourceFolder.SubFolders
        ParentID = GetFolderID(SourceFolder.Path) ' The record has just been added so get PK by name
    '   LogFilesFolders SubFolder.Name, SubFolder.Path, SubFolder.Type, ParentID, fft_Folder, FolderLevel
       SpanFolders SubFolder.Path, ParentID, FolderLevel
    Next SubFolder

    Set FileItem = Nothing
    Set SourceFolder = Nothing

End Sub

In the code notice the two locations where it gets the parentid. This may look strange but the reason is you need it at the beginning to log your forlder, but then in the recursive call it needs to change with each recursive call.

Also to sort your tree nicely. In qryFolder tree sort by FolderOrFile (desc) to put all the folders first, then sort by Name to get the folders and files in alphabetical order.

 

[zelarra821]

Yes now.

Let's see, another detail. You have removed hidden and system files, but not folders

        For Each FileItem In SourceFolder.Files
           If (FileItem.Attributes And 2) <> 2 And (FileItem.Attributes And 4) <> 4 Then
             FileCount = FileCount + 1
           End If
        Next FileItem

We would have to add a For Each Next for the folders and subfolders, but I don't know how to do it. It would be with the same If

 

[Dreamweaver]

Hi I've been watching this topic as @MajP know I'm interested but to buzzy at the moment to get involved.
You are suggesting system folders be hiddenfrom the list, sorry why would you have system folders in a folder set / working folders I.E. Where you created the main folder in the tree?
I have in the past looped through some very large folder sets but never found a system folder.
sorry @MajP for buttng in I'll go back to watching again LOL

Let's see, another detail. You have removed hidden and system files, but not folders

 

[zelarra821]

I see a hidden folder called Rubbish, which I think is created by Mega, the program I use to synchronize files in the cloud. So I want to put to remove hidden folders.