FileSystemObject Question

[Monsora83]

Just found out about FSO while looking for ways to import files names from folders/subfolders into access.

and I wanted to know if it is possible to change the Debug.Print fl.Name to populate a table with the results instead of populating the immediate window. (I did add the required Microsoft Scripting Runtime referance so it will run as it is)

And a second question, is it possible to put something like a WHERE criteria on this to only check for some specific text in the returned results.

Below is some code that I am playing with, it was copied from: http://www.ezidata.com.au/Tutorials/Working_with_Files.html

Private Sub FSO_Test_Click()
 
'declare the starting or root folder – you could get this from the Drives
Dim strRoot As String
strRoot = "C:\Silly folder names\Cat pictures to caption\If actions are louder than words then what language do they speak\I saw jesus in a llama"
 
'declare the variables for use with the Scripting library
Dim fso As New Scripting.FileSystemObject
Dim parent As Scripting.Folder
Dim children As Scripting.Folders
Dim child As Scripting.Folder
Dim fl As File
 
'get the folder from fso
Set parent = fso.GetFolder(strRoot)
 
'get the subfolders contained within the root folder
Set children = parent.SubFolders
 
'iterate through the subfolder under the root and display some data
For Each child In children
 
'iterate through the files
    For Each fl In child.Files
        Debug.Print fl.Name
    Next
Next
End Sub

 

[Addyman]

'iterate through the files

Dim strSQL as string

For Each fl In child.Files

strSQL = "INSERT INTO tblYourTable (YourFieldName) VALUES ('" & fl.Name & "')"

CurrentDb.Execute(strSQL)

    Next

 

[Addyman]

'iterate through the files

Dim strSQL as string, strSearchText as string, strFileName as string

strSearchText = "MySearchWord"

For Each fl In child.Files

strFileName = fl.Name

If InStr(strFileName, strSearchText) > 0 Then

strSQL = "INSERT INTO tblYourTable (YourFieldName) VALUES ('" & strFileName & "')"

CurrentDb.Execute(strSQL)

End If

    Next

 

[NigelShaw]

Hi,

you can add a recordset insert in place of the Debug.Print. The code you have collects the data andthe point of Debug.Print is where you put you function, code whatever to store it

Lets say the table is called tblFolderContents There will be an ID field and your field. Lets call it fldFolderPath

Private Sub FSO_Test_Click()
 
'declare the starting or root folder – you could get this from the Drives
Dim strRoot As String
strRoot = "C:\Silly folder names\Cat pictures to caption\If actions are louder than words then what language do they speak\I saw jesus in a llama"
 
'declare the variables for use with the Scripting library
Dim fso As New Scripting.FileSystemObject
Dim parent As Scripting.Folder
Dim children As Scripting.Folders
Dim child As Scripting.Folder
Dim fl As File
 
[B]'declare your Recordset Variables[/B]
[B]Dim db As DAO.Database[/B]
[B]Dim rs As DAO.Recordset[/B]
[B]Set db = CurrentDb()[/B]
[B]Set rs = db.OpenRecordset("tblFolderCOntents", dbOpenDynaset)[/B]
 
'get the folder from fso
Set parent = fso.GetFolder(strRoot)
 
'get the subfolders contained within the root folder
Set children = parent.SubFolders
 
'iterate through the subfolder under the root and display some data
For Each child In children
 
'iterate through the files
    For Each fl In child.Files
        'Debug.Print fl.Name
[B]With rs[/B]
[B].AddNew[/B]
[B]!fldFolderPath = fl.Name[/B]
[B].Update[/B]
[B]End With[/B]
    Next
Next
[B]rs.Close[/B]
[B]db.Close[/B]
[B]Set rs = Nothing[/B]
[B]Set db = Nothing[/B]
End Sub

To Answer question 2, Yes you could. A few ways to do it-

Select Case option-

Select Case fl.Name
Case "Criteria 1"
'want to store this name
With rs
 .AddNew
 !fldFolderPath = fl.Name
 .Update
End With
 
Case "Criteria 2"
'Dont want to store this name
 
end Select

If option

If fl.Name = "Criteria1" Then
'save record
Else
'dont save record
End If

HTH

Nigel

 

[Monsora83]

Awesome! Both of those options will work great. Thanks for the help.

 

[sxschech]

Was wondering is it possible to use the FSO data directly in a select query without having to save in a table first?