Filesystem -> Database

BETTY
BETTY used Ask the Experts™
on
Hi, guys, Spain talking...

That's what I'm trying to do: I want to catalog some freewares in a CD. I have collected them in a folder tree. Each branch typically contains at least one exe file and some html, txt and / or dll that can vary or even not exist (but at least one exe).

What I want to code is a function that receives the root path that contains all branches (i.e. c:\MyCD\) (but in order to reuse I want to pass it as parameter i.e. from a dirlistbox). Then the function recursively checks each branch, and performs two jobs: 1) determine the name and size of the branch (folder) and 2) obtain the name of the exe inside it. Then it stores the three values in fields of a database.

I'm sure it's easy for you, experts, but I've never worked with scripting.filesystemobject nor with API FindFileFirst. I don't know which method is better (I don't need extreme speed, it's only to catalog 650 MB in a let's say 200-branch tree). I would appreciate code better than discussion about one or other method, as I've said I've never dealed with them and I've no idea of methods and API calls.

Ah! Very important: I work with VB 5 (I prefer 5 paid than 6 or NET pirated, you know) I say it because I've heard some things can only be made with VB6.

Thanks in advance. If you have any more question, please let me know. Greetings from the Olympic Barcelona, Spain.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Ok i assume you know how to stroe stuff in a DB so i left that part out

Here is a function that will work with the Filesystemobject
'---Code
Dim ofso As New FileSystemObject
Private Type Folderinfo
    Folder As String
    Size As Long
    Document As String
    executable As String
End Type

Dim colFolderinfo As New Collection

Private Sub getInfo(Folder As String)
Dim oFolder As Folder
Dim myFile As File
Dim loopFolder As Folder
Dim myFolderinfo As Folderinfo

Set oFolder = ofso.GetFolder(Folder)

If oFolder.Files.Count > 0 Then 'Get info on files if there are any
    myFolderinfo.Folder = oFolder.Path
    For Each myFile In oFolder.Files 'sum up stats
        With myFolderinfo
            .Size = .Size + myFile.Size
            If LCase(Right(myFile.Name, 3)) = "exe" Then .executable = myFile.Name
            If LCase(Right(myFile.Name, 4)) = "html" Or LCase(Right(myFile.Name, 3)) = "htm" Then .Document = myFile.Name
        End With
    Next
    colFolderinfo.Add myFolderinfo
End If

For Each loopFolder In oFolder.Folders 'call recursive
    getInfo (loopFolder.Path)
Next

End Sub

Author

Commented:
I've been checking your code and at first it didn't work.

I've corrected the main problem reading in the www some articles. It's on the line:

For Each loopFolder In oFolder.Folders 'call recursive

and it has to be:

For Each loopFolder In oFolder.SubFolders 'call recursive

The other problem is on the line:

  colFolderinfo.Add myFolderinfo

but I'm not using the collection but the recordset in the database.
The problem is when I execute the code, a compilation error pops telling me "Type mismatch in ByRef argument" and highlighting "myFolderinfo".

Now I'm trying to modify your code to accept more than one exe in a folder, as I can't have the total size of the folder until loop finishes, and only last exe is stored then. I think I'm going to implement two loops for each folder, one to have its size and other to locate the exes and put them into the database.

When finished, I'll deal with the points of this question.
See you soon!

Commented:
hehe that was typed only in the Response window, not in VB , so 2 errors are not really that bad i guess ;)

Dont create a RS variable in the recursive function. Create it on the form lvl , so you can access it without opening the RS every time.

To get more then one exe you could change it like this (without changing the structure of the DB)

change

 If LCase(Right(myFile.Name, 3)) = "exe" Then .executable = myFile.Name
to --->

 If LCase(Right(myFile.Name, 3)) = "exe" Then .executable = .executable & myFile.Name & vbtab

Same goes for the document entry...

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
It's another approach, you mean at the end of the loop I process .executable string and spare it in several pieces, I suppose.

And the errors comment is not a criticism, ot was only to warn you if you were also using the code.

Keep trying, more news soon.

Commented:
if you build the executable string like this :

If LCase(Right(myFile.Name, 3)) = "exe" Then .executable = .executable & myFile.Name & vbtab

you can use

arFiles = split (myObj.executable)

and you will get an array of executable files.

then you can use ubound(arFiles) to count them...


Author

Commented:
Hey, rdrunner, all is working by now, so here are the points!

Thanks a lot.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial