Loop thru files in specified directory (1 Viewer)

teel73

Registered User.
Local time
Today, 06:56
Joined
Jun 26, 2007
Messages
205
I would like to loop through all the files in a specified directory and add the filenames to a table. The example I'm given doesn't work for me. Here's what I have:

Code:
Sub ShowFolderList(folderspec)
    Dim fs, f, f1, fc, s
    folderspec = "C:\Documents and Settings\e3utbl\Desktop\FTPTest\Audit_Docs_Mgmt"
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set fc = f.Files
    For Each f1 In fc
        s = s & f1.Name
        s = s & vbCrLf
    Next
    Debug.Print s
End Sub

Can someone help me to determine what I'm doing wrong?
 

stopher

AWF VIP
Local time
Today, 13:56
Joined
Feb 1, 2006
Messages
2,396
The code you have is designed to print the list of files to the VBA Immediate window. Are you saying it's not doing that?

If you are wanting to know how to use this code to store the files to a table then here's one way:

Code:
Public Sub ShowFolderList(folderspec)
Dim fs, f, f1, fc, s
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblFiles")

folderspec = "C:\Documents and Settings\e3utbl\Desktop\FTPTest\Audit_Docs_Mgmt"

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files

For Each f1 In fc
    rs.AddNew
    rs.Fields("FileName") = f1.Name
    rs.Update
Next

Set rs = Nothing

End Sub

In the above example,the table is called tblFiles and the fieldname is called FileName.

hth
Chris
 

teel73

Registered User.
Local time
Today, 06:56
Joined
Jun 26, 2007
Messages
205
Yes. Its not doing that. That is what I wanted to do was test to see if the loop even worked but its not doing that.
 

vbaInet

AWF VIP
Local time
Today, 13:56
Joined
Jan 22, 2010
Messages
26,374
stopher code should work. What is happening when you run the code? You get an error message?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:56
Joined
Sep 12, 2006
Messages
15,613
you can use dir. this bit of aircode will be close to what you want
the only problem with dir is that it is not recursive, so you cant examine subfolders at the same time.


Code:
fname = dir("somepath",vbnormal)
while fname<>""
   process the file
   fname = dir()  'get the next file
wend
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:56
Joined
Jan 23, 2006
Messages
15,362
Further to my reference to
see http://allenbrowne.com/ser-59.html
'
Here are comments from the Allen Browne procs.

It does deal with subfolders, is recursive and offers various outputs

The code is at the site.

'-----------------------------------------------------------------
' Procedure : ListFiles
' DateTime : 2007-09-17 15:24
' Author : drawbrij
' Purpose : This description was obtained from Internet - Allen Browne
' see http://allenbrowne.com/ser-59.html
'
' How it works

'ListFiles() is the main routine.
'It uses a collection to demonstrate how the file names can
'all be collected, and then output in different ways
'(list box, table, immediate window.)

'FillDir() does the work of looping through the files
'in a folder that meet the file specification, and
'adding them to the collection. If we are to include the
'subfolders as well, the second part loops through all
'the files again to identify those that are directories.
'It ignores the "." and ".." entries, uses GetAttr() to
'identify the directories, and adds them to the colFolders
'collection. Then for each of the folders in this collection,
'the function calls itself again to handle the files in
'that folder. If that folder contains subfolders also,
'the function will continue to call itself recursively, to
'whatever depth is required.

'The TrailingSlash() function just ensures that the folder
'names we are processing end with the slash character.


Here is a test routine.
Code:
'---------------------------------------------------------------------------------------
' Procedure : TestAllenBrowneListFiles
' DateTime  : 2007-09-19 16:20
' Author    : drawbrij
' Purpose   : This proc is intended for Testing the
'             ListFiles procedure obtained from Allen Browne's website.
'---------------------------------------------------------------------------------------
'
Sub TestAllenBrowneListFiles()
   On Error GoTo TestAllenBrowneListFiles_Error
 
ListFiles "c:\users\jack\", "*.mdb", True

   On Error GoTo 0
   Exit Sub

TestAllenBrowneListFiles_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure TestAllenBrowneListFiles of Module AllanBrowneDirectoryStuff"
End Sub

And output to immediate window.

c:\users\jack\A2K\db1New.mdb
c:\users\jack\A2K\db1New2009.mdb
c:\users\jack\A2K\db1New2009_Backup.mdb
c:\users\jack\A2K\Example Stock Control.mdb
c:\users\jack\A2K\Get_Coordinates.mdb
c:\users\jack\A2K\MouseOverPopups.mdb
c:\users\jack\A2K\MultiLingualSample.mdb
c:\users\jack\A2K\MyForumsAndPwd.mdb
c:\users\jack\A2K\ProgressMeter.mdb
c:\users\jack\A2K\RawLoaderStandardTemplate_v2_Backup.mdb
c:\users\jack\A2K\TS001018458.mdb
c:\users\jack\A2K\VBEStuff.mdb
c:\users\jack\Desktop\MyForumsAndPwd.mdb
c:\users\jack\Documents\AWF_Boycie.mdb
c:\users\jack\Documents\db1.mdb
c:\users\jack\Documents\xml.mdb
c:\users\jack\Downloads\access forum database - BLANK.mdb
c:\users\jack\Downloads\Database1.mdb
c:\users\jack\Downloads\EvalDates.mdb
c:\users\jack\Downloads\Help.mdb
c:\users\jack\Downloads\lottttttttt2.mdb
c:\users\jack\Downloads\New_Efficiency.mdb
c:\users\jack\Downloads\SalesandOrders.mdb
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:56
Joined
Jan 23, 2006
Messages
15,362
I just ran your code - I did hard code my folder
Code:
Sub ShowFolderList() ' (folderspec)
    Dim fs, f, f1, fc, s
    'folderspec = "C:\Documents and Settings\e3utbl\Desktop\FTPTest\Audit_Docs_Mgmt"
    folderspec = "C:\users\jack\a2k\"
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set fc = f.files
    For Each f1 In fc
        s = s & f1.name
        s = s & vbCrLf
    Next
    Debug.Print s
End Sub

and it worked fine

BN 093_2011_OGNEAN-1.xml
Book1.xls
db1New.mdb
db1New2009.ldb
db1New2009.mdb
db1New2009_Backup.mdb
Example Stock Control.ldb
Example Stock Control.mdb
Get_Coordinates.mdb
MontyJC.xsl
MouseOverPopups.mdb
MultiLingualSample.mdb
MyForumsAndPwd.mdb
ProgBar.exe
ProgressMeter.mdb
RawLoaderStandardTemplate_v2_Backup.mdb
TS001018458.mdb
VBEStuff.mdb


Do you have a reference set to
Microsoft Scripting Runtime?
 

vbaInet

AWF VIP
Local time
Today, 13:56
Joined
Jan 22, 2010
Messages
26,374
You're right there jdraw, both work. So it will be useful to know what actually happens when teel73 runs the code, whether it throws an error or not.

By the way, you may have missed that fs is using late binding so a reference is not needed.
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:56
Joined
Jan 23, 2006
Messages
15,362
Good point vbaINet _ I missed the late binding.

I was wondering why he didn't get a "missing reference".
Thanks
 
Last edited:

teel73

Registered User.
Local time
Today, 06:56
Joined
Jun 26, 2007
Messages
205
Hi guys.. I didn't have the reference to Microsoft Scripting Runtime .. thanks all.
 

vbaInet

AWF VIP
Local time
Today, 13:56
Joined
Jan 22, 2010
Messages
26,374
If you're using the FileSystemObject example then you didn't need to set a reference. The code utilises what is called Late Binding which creates an object on-the-fly without the need of a reference.
 

ironpepsi

New member
Local time
Today, 08:56
Joined
Jan 31, 2017
Messages
2
in trying to use Allen's code how different would this be if I wanted to list all of the subfolders in a directory to a table?
 

VDH

New member
Local time
Today, 14:56
Joined
Feb 9, 2017
Messages
1
Hi All I have try your solutions and it work great. But what if you want to list the files in sub directory also
thanks for good input
 

ITGvbalearner

New member
Local time
Today, 13:56
Joined
Nov 15, 2017
Messages
1
Good afternoon,

Can I get your advice if i wanted to use this code to run on a mac operating system how would this be possible due to the Mac not using a drive location?
 

Wayne

Crazy Canuck
Local time
Today, 09:56
Joined
Nov 4, 2012
Messages
176
I used to save pictures related to a job as an attachment in an attachment field. My database grew in size dramatically, so I set up a bound subform on my main Orders form to save each picture as a hyperlink. The form and subform are linked by the field "OrderNumber". The pictures for each order are saved in a directory unique to that order number.

At first, I had to add each picture individually to the subform, but for large orders, that took time. I used the following code to get all of the pictures at once and populate the table/subform. And it works well. However, if you click the button again, it adds them all again, and again - as many times as you click it. This can cause problems with other users. What I am looking for is way to first check if they exist in the table already, and if so, not to add it again. This is the code I am using to add the pictures:

Code:
Private Sub btnGetPictures_Click()

    On Error GoTo Err_btnGetPictures_Click

    Dim folderspec As String
    Dim fs As Object
    Dim f As Object
    Dim f1 As Object
    Dim fc As Object
    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset("tblJobPix")
    
    folderspec = "C:\FilePath\Job Pix\" & [Forms]![frmOrders]![CustNumber] & "\" & [Forms]![frmOrders]![OrderNumber] & " " & [Forms]![frmOrders]![ClientUserlastName]
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set fc = f.Files
    
    For Each f1 In fc
        rs.AddNew
        rs.Fields("OrderNumber") = [Forms]![frmOrders]![OrderNumber]
        rs.Fields("ImageFilePath") = (folderspec & "\" & f1.Name)
        rs.Update
    Next
    Me.Requery
    
    Set rs = Nothing
    
Exit_btnGetPictures_Click:
    Exit Sub
    
Err_btnGetPictures_Click:
    MsgBox Err.Description, vbInformation, "Attention"
    Resume Exit_btnGetPictures_Click
    
End Sub

Should I be using an If Len statement first to check the existence of the file name first? And I am not sure how to write it. Any help here would be appreciated.
 

Users who are viewing this thread

Top Bottom