Get File names from a sub folders sub folder

[gemma-the-husky]

check your code references.

in the module, do tools/references

DAO should be checked, above ADO

maybe try
dim db as dao.database
dim rst as dao.recordset


I didn't think I had changed anything there though - I just moved the code to a different area - so I don't see why it would not work now.

Just re-checked, and it probably is that. JamesMc had originally used dao.recordset.


What it is, is that there are two libraries of functions to access tables and queries, DAO and ADO - both are similar and use the same commands but are not completely interchangeable. It didn't help the MS started by making DAO the "default" version, then changed to ADO, then changed back again - so it is easy to get the wrong version by accident.

 

[JohnLee]

Hi Gemma,

Thanks for your speedy response, I had the DAO checked in my references.

It was the .....

Dim db As DAO.Database
Dim rst As DAO.Recordset

That sorted it, amazing how something so simple can cause big problems.

Yours and James help has been excellent. Thank you both.

John

 

[threadbooster]

OK here's a fully tested version -

it needs a table called

tblfiles, with fields

folder, string 255
filename, string 255

It has some error checking. When I tried to run this on my disk D: it threw an error on folder "D:\ system volumn information" - so I added some error-checking

Option Compare Database
Option Explicit

Dim db As Database
Dim rst As Recordset
Dim files As Long

[COLOR="Red"]'This is the recursive folder tree walker sub[/COLOR]

sub listfiles(root As String)

Dim fname As String
Dim Fso As Object, Fldr As Object, SubFldr As Object, F As Object

If Right(root, 1) <> "\" Then root = root & "\"  [COLOR="red"]'just in case there is no trailing \[/COLOR]

Set Fso = CreateObject("scripting.filesystemobject")
Set Fldr = Fso.GetFolder(root)[COLOR="red"] 'use this current folder[/COLOR]

   [COLOR="red"] 'now iterate the files - error handler just in case[/COLOR]
    On Error GoTo failFILE
    For Each F In Fldr.files
       [COLOR="red"]'append any file to the table[/COLOR]        
        rst.AddNew
        rst!folder = root
        rst!filename = F.Name
        rst.Update
        files = files + 1
nextfile:
    Next
 
   [COLOR="red"] 'now iterate the folders - error handler just in case[/COLOR]
    On Error GoTo failFOLDER
    For Each SubFldr In Fldr.SubFolders
       listfiles (root & SubFldr.Name)
nextfolder:
    Next

Exit Sub

failFILE:
    MsgBox ("Unable to examine file in folder " & root & vbCrLf & _
        "Error: " & Err & "  Desc: " & Err.Description)
    Exit Sub
    Resume nextfile

failFOLDER:
    MsgBox ("Unable to examine folder " & root & vbCrLf & _
        "Error: " & Err & "  Desc: " & Err.Description)
[COLOR="Red"]   'Note I aborted here with exit sub, rather than continue the loop - 
   'when I tried it, I got a problem trying to continue the loop, 
   'with the fso loop dropping out of scope.
    Exit Sub
    Resume nextfolder

End Sub



Sub main()
Set db = CurrentDb
Set rst = db.OpenRecordset("tblFiles", dbOpenDynaset)
files = 0

listfiles ("D:\")

rst.Close
Set rst = Nothing
Set db = Nothing

MsgBox ("Finished: " & files & " files found. ")

End Sub

sorry to up the old thread
but i got the problem same with this

i already try your code

but i want to the subfolder is input to different field to
now the result are like this :

folder | filename
folder1/subfolder1/subfolder2/ | file.pdf

i want to like this

folder | subfolder1 | subfolder2 | subfolder3 | subfolder4| filename
folder1/subfolder1/subfolder2/ | subfolder1 | subfolder 2 | subfolder 3 | subfolder4 |file.pdf

how i must change the code?

 

[threadbooster]

sorry to up the old thread
but i got the problem same with this

i already try your code

but i want to the subfolder is input to different field to
now the result are like this :

folder | filename
folder1/subfolder1/subfolder2/ | file.pdf

i want to like this

folder | subfolder1 | subfolder2 | subfolder3 | subfolder4| filename
folder1/subfolder1/subfolder2/ | subfolder1 | subfolder 2 | subfolder 3 | subfolder4 |file.pdf

how i must change the code?

please help me to solve this problem