Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 54 votes, 5.00 average. Display Modes
Old 03-07-2011, 11:07 AM   #1
teel73
Newly Registered User
 
Join Date: Jun 2007
Posts: 205
Thanks: 1
Thanked 0 Times in 0 Posts
teel73 is on a distinguished road
Loop thru files in specified directory

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?

teel73 is offline   Reply With Quote
Old 03-07-2011, 12:02 PM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,728
Thanks: 63
Thanked 1,920 Times in 1,870 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Loop thru files in specified directory

There is a routine written by Allen Browne to ListFiles in a directory and do various outputs
see http://allenbrowne.com/ser-59.html


If you intend to stay with fso
see http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx

If you use fso, you must include a reference to the
Microsoft Scripting Runtime library

Last edited by jdraw; 03-07-2011 at 12:33 PM.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
ncrcn (01-02-2019)
Old 03-07-2011, 12:28 PM   #3
stopher
AWF VIP
 
stopher's Avatar
 
Join Date: Feb 2006
Location: Southampton, UK
Posts: 2,396
Thanks: 20
Thanked 311 Times in 292 Posts
stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough
Re: Loop thru files in specified directory

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

stopher is offline   Reply With Quote
Old 03-08-2011, 05:26 AM   #4
teel73
Newly Registered User
 
Join Date: Jun 2007
Posts: 205
Thanks: 1
Thanked 0 Times in 0 Posts
teel73 is on a distinguished road
Re: Loop thru files in specified directory

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.
teel73 is offline   Reply With Quote
Old 03-08-2011, 05:48 AM   #5
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Loop thru files in specified directory

stopher code should work. What is happening when you run the code? You get an error message?
vbaInet is offline   Reply With Quote
Old 03-08-2011, 06:35 AM   #6
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,462
Thanks: 51
Thanked 950 Times in 918 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Loop thru files in specified directory

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
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 03-08-2011, 06:54 AM   #7
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,728
Thanks: 63
Thanked 1,920 Times in 1,870 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Loop thru files in specified directory

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.

Quote:
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_Bac kup.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 is offline   Reply With Quote
Old 03-08-2011, 07:08 AM   #8
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,728
Thanks: 63
Thanked 1,920 Times in 1,870 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Loop thru files in specified directory

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

Quote:
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?
jdraw is offline   Reply With Quote
Old 03-08-2011, 07:16 AM   #9
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Loop thru files in specified directory

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.
vbaInet is offline   Reply With Quote
Old 03-08-2011, 07:35 AM   #10
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,728
Thanks: 63
Thanked 1,920 Times in 1,870 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Loop thru files in specified directory

Good point vbaINet _ I missed the late binding.

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

Last edited by jdraw; 03-08-2011 at 09:25 AM.
jdraw is offline   Reply With Quote
Old 03-09-2011, 07:42 AM   #11
teel73
Newly Registered User
 
Join Date: Jun 2007
Posts: 205
Thanks: 1
Thanked 0 Times in 0 Posts
teel73 is on a distinguished road
Re: Loop thru files in specified directory

Hi guys.. I didn't have the reference to Microsoft Scripting Runtime .. thanks all.
teel73 is offline   Reply With Quote
Old 03-09-2011, 07:50 AM   #12
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Loop thru files in specified directory

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.
vbaInet is offline   Reply With Quote
Old 02-01-2017, 08:54 AM   #13
ironpepsi
Newly Registered User
 
Join Date: Jan 2017
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
ironpepsi is on a distinguished road
Re: Loop thru files in specified directory

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?
ironpepsi is offline   Reply With Quote
Old 02-09-2017, 06:01 AM   #14
VDH
Newly Registered User
 
Join Date: Feb 2017
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
VDH is on a distinguished road
Re: Loop thru files in specified directory

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
VDH is offline   Reply With Quote
Old 11-15-2017, 06:48 AM   #15
ITGvbalearner
Newly Registered User
 
Join Date: Nov 2017
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
ITGvbalearner is on a distinguished road
Re: Loop thru files in specified directory

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?

ITGvbalearner is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Looping through files in a directory rberkers Modules & VBA 4 09-09-2010 10:27 PM
Looping through files in a directory chrism84 Modules & VBA 2 11-30-2007 09:20 AM
Import all excel files within a given directory? rsmonkey Modules & VBA 3 10-09-2007 02:04 AM
Copy a directory with files and incl sub directory's with files. Dongio General 2 04-01-2004 05:32 AM
[SOLVED] Copy files from 1 directory 2 another Geovanny Modules & VBA 1 11-12-2002 01:14 PM




All times are GMT -8. The time now is 06:33 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World