Challenge - Search function!!!

[themanof83]

Please guys I am begging for some help here ..... as I have no idea where to start.

Basically, I have a list of tests each test is an individual record and within the record there are fields such as test name and test directory (which, using hyperlinks, is used to find the actual test files (PDF's in a directory)).

The tests all exist in a top level directory. Under this directory there are a number of sub directories each associated with a test (field - test directory) within these directories lies the test PDF file.

What I would like to do (using VB) is have some sort of function that:

a) searches the top level directory,
b) populates all the sub directory names in the appropriate fields for the records,
c) populates all the test files within the sub directories.

I know its a lot to ask but I would very much appreciate any help. Thanks in advance.

Ashley

 

[SrPuma]

well what ur asking basicy sounds like a treeview control (windows explorer folder tree)
i didnt find any awnsers on this forum since i asked the same thing but i made it in Access 2003
heres the solution i found to my problem (u might as well see somethigns there that u wont need so just delete the if "_espanha" and similars
i hope this helps you on something

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Dim tvn As Node
Dim oFso As Object
Dim oFolder
Dim oSubFolder
Dim oSubSubFolder
Dim oSubSubSubFolder
Dim oFile
Dim Folder As String

Function Arvore()
ListView7.ListItems.Clear ' this is where the files inside the selected folder appear'
Tree1.Nodes.Clear
Set oFso = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFso.GetFolder("path name here")
Folder = oFolder
Set tvn = Tree1.Nodes.Add(, tvwParent, Folder, "Name u want to appear on the root", 10)
'subfolders code below in case of need

For Each oSubFolder In oFolder.subfolders
fPath = oFolder + "\" + oSubFolder.Name
Set tvn = Tree1.Nodes.Add(Folder, tvwChild, fPath, oSubFolder.Name, 5)
SubFolder = oSubFolder

For Each oSubSubFolder In oSubFolder.subfolders
dPath = SubFolder + "\" + oSubSubFolder.Name

If oSubSubFolder.Name = "Imagens" Then
Set tvn = Tree1.Nodes.Add(SubFolder, tvwChild, dPath, oSubSubFolder.Name, 2)
ElseIf oSubSubFolder.Name = "PDFS" Then
Set tvn = Tree1.Nodes.Add(SubFolder, tvwChild, dPath, oSubSubFolder.Name, 3)
ElseIf oSubSubFolder.Name = "_Espanha" Then
Set tvn = Tree1.Nodes.Add(SubFolder, tvwChild, dPath, oSubSubFolder.Name, 7)
ElseIf oSubSubFolder.Name = "_França" Then
Set tvn = Tree1.Nodes.Add(SubFolder, tvwChild, dPath, oSubSubFolder.Name, 8)
ElseIf oSubSubFolder.Name = "_Angola" Then
Set tvn = Tree1.Nodes.Add(SubFolder, tvwChild, dPath, oSubSubFolder.Name, 6)
ElseIf oSubSubFolder.Name = "_Portugal" Then
Set tvn = Tree1.Nodes.Add(SubFolder, tvwChild, dPath, oSubSubFolder.Name, 9)
ElseIf oSubSubFolder.Name = "_S. Tome e Principe" Then
Set tvn = Tree1.Nodes.Add(SubFolder, tvwChild, dPath, oSubSubFolder.Name, 1)
Else
Set tvn = Tree1.Nodes.Add(SubFolder, tvwChild, dPath, oSubSubFolder.Name, 5)
End If
​

SubSubFolder = oSubSubFolder

For Each oSubSubSubFolder In oSubSubFolder.subfolders
sPath = SubSubFolder + "\" + oSubSubSubFolder.Name
Set tvn = Tree1.Nodes.Add(SubSubFolder, tvwChild, sPath, oSubSubSubFolder.Name, 4)
Next
​

Next
​

Next
​

End Function

Private Sub Tree1_NodeClick(ByVal Node As Object)
Dim lvwItem As ListItem
ListView7.ListItems.Clear
Set oFso = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFso.GetFolder(Me.Tree1.SelectedItem.Key)
Folder = oFolder
For Each oFile In oFolder.Files

'the code below here is just to give icons to the files that will appear in the listview control'

If oFile.Type = "Microsoft Excel Worksheet" Then
Set lvwItem = ListView7.ListItems.Add(, , oFile.Name, 1, 1)
ElseIf oFile.Type = "Microsoft Office Access Application" Then
Set lvwItem = ListView7.ListItems.Add(, , oFile.Name, 3, 3)
ElseIf oFile.Type = "Adobe Acrobat Document" Then
Set lvwItem = ListView7.ListItems.Add(, , oFile.Name, 4, 4)
ElseIf oFile.Type = "Microsoft Word Document" Then
Set lvwItem = ListView7.ListItems.Add(, , oFile.Name, 5, 5)
ElseIf oFile.Type = "Imagem JPEG" Then
Set lvwItem = ListView7.ListItems.Add(, , oFile.Name, 2, 2)
ElseIf oFile.Type = "Imagem de mapa de bits" Then
Set lvwItem = ListView7.ListItems.Add(, , oFile.Name, 2, 2)
End If
Next

End Sub

Private Sub ListView7_Click()
' this is just in case of clicking on the listview without any items there...it will give u an error if theres nothing to click without the code below(at least in mine is like this xD)
Me.Fl.Value = ""
If ListView7.ListItems.Count = 0 Then
MsgBox "No files found on the folder"
Else
Me.Fl.Value = Me.Tree1.SelectedItem.Key + "\" + Me.ListView7.SelectedItem.Text
End If
End Sub

Private Sub ListView7_DblClick()
' the Fl is a hidden textbox that i use to save the full path of the file to execute it'
Me.Fl.Value = ""
Me.Fl.Value = Me.Tree1.SelectedItem.Key + "\" + Me.ListView7.SelectedItem.Text
If Me.ListView7.SelectedItem.Icon = 1 Then
ShellExecute Me.hwnd, "Open", Me.Fl, 0&, 0&, vbMinimized
End If
End Sub

 

[themanof83]

Looks good SrPuma.
Excuse me for asking but could you just quickly tell me what the outcome of the function is, just so I understand it better?
Ash

 

[CyberLynx]

Something like this Ashley really isn't all that difficult to do but, you haven't really given us any sort of structure to go on.

- What does the table look like that all this data will go into?
- What disk data goes into what table field?
- Do the Sub Folders go into a separate table field and the test file name in another?
- Or does the Path and test file name go into a single field?
- Would or could the Test files ever be nested deeper into the Root folder other than single sub folder depth?

Make a small sample DB and post it here. Let's see what you have accomplished this far.

.

 

[SrPuma]

Looks good SrPuma.
Excuse me for asking but could you just quickly tell me what the outcome of the function is, just so I understand it better?
Ash

Well the function is to scan the path of the directory u want and turns it into a tree like explorer thingy xD....this was made so i could load the record corresponding to the highlighted tree node filling the form with the record data from a billing company

 

[themanof83]

Re: Cyberlinx

Geez.... ouch!

Ok, I've attached my table that I'm working with and here's the answers to your questions.

- The file name located in the sub directory will be put into the Test field. But must only be the file name without the extension. i.e. 1a.01.10.GM not 1a.01.10.GM.pdf. (I do have a valid reason for this!)
- The sub folders go into the field Document Folder.
- The test files will only ever be nested one level down
i.e. top level folder -> sub folder -> test files

I appreciate the help, thanx.

 

[themanof83]

Something like this Ashley really isn't all that difficult to do but, you haven't really given us any sort of structure to go on.

- What does the table look like that all this data will go into?
- What disk data goes into what table field?
- Do the Sub Folders go into a separate table field and the test file name in another?
- Or does the Path and test file name go into a single field?
- Would or could the Test files ever be nested deeper into the Root folder other than single sub folder depth?

Make a small sample DB and post it here. Let's see what you have accomplished this far.

.

See Earlier reply.....

 

[CyberLynx]

Sorry for the late response.

Here is your sample back to you slightly modified to demonstrate what I think it is you wanted. The returned attached sample DB now contains a few Forms and SubForms. One Form (SearchForm) is a simple Table data Search based on a selected Test Title and the Section that Title resides in.

The other Form of interest is the PopulateTable Form. This Form will search the Supplied Disk Root Folder (directory) for Sub-Folders and the .PDF Files within those Sub-Folders (files directly in Root are ignored). Found Sub-Folders and Files are compared to items already in Table and if they exist, they are ignored. Only new found items are added to Table.

A SubForm is also supplied in the PopulateTable Form so as to display the newly added Table items and allow the User to edit the records so as to perhaps supply a Title, etc.

I am somewhat unsure about the Table field named Section. I took this to actually be a Root Folder name and therefore the Root Folder name is also added to Table in the Section field. If this is not the case then you can easily remove this by going into code and accessing the GetFiles Function. Modify the StrgSQL string variable contents to suit your needs. This variable contains the INSERT INTO SQL statement to apply the new data to Table. Right now it looks like this:

StrgSQL = "INSERT INTO Tests ([Test],[Section],[Document Folder]) " & _
                                "VALUES ('" & TestName & "','" & RootFolder & "','" & _
                                TestFolder & "');"

If you don’t want to place the Root Folder Name into the Section field then change the string to this:

StrgSQL = "INSERT INTO Tests ([Test],[Document Folder]) " & _
                                "VALUES ('" & TestName & "','" & TestFolder & "');"

The code in the sample DB is fully commented so you can see what's going on.

Hope this helps somewhat.

.

 

[themanof83]

Awesome work Cyberlinx!!!!!
Still unsure how u think that is not difficult, but hey I appreciate the help immensly.
Thanks once again.