Kiwiman
Registered User
- Local time
- Today, 08:19
- Joined
- Apr 27, 2008
- Messages
- 799
I Have been playing around with this now to try and loop through all subfolders (and their subfolders etc) of a directory, looking for csv files, so I can import the filenames into a table.
This is the first time I have played around with File Scripting objects. I found mot of this code on this site, and added the some code in order to load the necessary info into my table.
This code works for the subfolders of the named directory, but ignores any subfolders of the subfolder. If you get my meaning.
I'm not sure how on the syntax on how to search all subfolders \ subfolders of a chosen directory. There are alot of posts regarding looping through directories, but I can't quite get the job done. Any help would be appreciated.
This is the first time I have played around with File Scripting objects. I found mot of this code on this site, and added the some code in order to load the necessary info into my table.
This code works for the subfolders of the named directory, but ignores any subfolders of the subfolder. If you get my meaning.
I'm not sure how on the syntax on how to search all subfolders \ subfolders of a chosen directory. There are alot of posts regarding looping through directories, but I can't quite get the job done. Any help would be appreciated.
Code:
Sub printFolders()
Dim FS As New FileSystemObject
Dim objFld As Folder
Dim objFile As File
Dim strSQL As String
Dim intItem As Integer
Dim ctlList As Access.ListBox
Dim lngCount As Long
Dim strFolder As String
Dim strFile As String
Dim FSfolder As Folder
Dim Subfolder As Folder
Dim strName As String
Dim i As Integer
Set FSfolder = FS.GetFolder("C:\Documents and Settings\LLockett.CERIDIANHR\My Documents\Work\AP Cleanse\")
Debug.Print FSfolder
For Each objFile In FSfolder.Files
Select Case Right(objFile.Name, 3)
Case Is = "CSV"
strSQL1 = "INSERT INTO tblFiles ( FIleName,FilePath) SELECT '" & objFile.Name & "','" & FSfolder & "'"
DoCmd.RunSQL strSQL1
Case Else
End Select
lngCount = lngCount + 1
Next
Stop
i = 0
For Each Subfolder In FSfolder.SubFolders
DoEvents
strName = FSfolder & "\" & Subfolder.Name & "\"
Debug.Print strName
Set objFld = FS.GetFolder(strName)
Debug.Print objFld
For Each objFile In objFld.Files
Debug.Print objFile
Select Case Right(objFile.Name, 3)
Case Is = "CSV"
strSQL1 = "INSERT INTO tblFiles ( FIleName,FilePath) SELECT '" & objFile.Name & "','" & strName & "'"
DoCmd.RunSQL strSQL1
Case Else
End Select
lngCount = lngCount + 1
Next
'do your rs stuff here
'Debug.Print Subfolder.Name
i = i + 1
Next Subfolder
Set FSfolder = Nothing
End Sub