asked on

Reading the Contents of a Directory In Access VBA

From within my Access 2003 application I would like a routine to read a list of the contents of a directory, including file name, file type, file size and date/time stamp of the file.

I will pass the directory name (example: 'C:\Program\Test') to the routine and based on the file name and file type I will initiate pertinent logic.

PatHartman

I use FSO (file system object) to do this but I'm not sure if this library was available for A2003 and I don't have any way to find out.

Using FSO requires a reference to Microsoft Scripting Runtime. Open any code module and see if you can find that library loaded. If you can, I'll look for an example tomorrow if no one has posted one by then. I'm off to play Bridge.

ASKER CERTIFIED SOLUTION

[ fanpages ]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Joe Howard

Here are two options:

Sub Demo(ByVal strFolder As String)
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
     
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(strFolder)
     
    For Each objFile In objFolder.Files
        Debug.Print "Name: " & objFile.Name, "Size: " & objFile.Size, "Type: " & objFile.Type, "Date last modified: " & objFile.DateLastModified; "Date created: " & objFile.DateCreated, "Date last accessed: " & objFile.DateLastAccessed,
    Next
     
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
End Sub

Sub Demo2(ByVal strFolder As String)
    Dim objShell As Object
    Dim objDir As Object
    Dim objFile As Object

    Set objShell = CreateObject("Shell.Application")
    Set objDir = objShell.Namespace(strFolder)

    For Each objFile In objDir.Items
        Debug.Print "Name: " & objDir.GetDetailsOf(objFile, 0), "Size: " & objDir.GetDetailsOf(objFile, 1), "Type: " & objDir.GetDetailsOf(objFile, 2), "Date last modified: " & objDir.GetDetailsOf(objFile, 3), "Date created: " & objDir.GetDetailsOf(objFile, 4), "Date last accessed: " & objDir.GetDetailsOf(objFile, 5)
    Next

    Set objShell = Nothin
    Set objDir = Nothin
    Set objFile = Nothin
End Sub

Open in new window

crystal (strive4peace)

since others have given you code to get information about a file, I will add code to loop through a directory and read filenames into an array then loop through the array

Sub LoopFiles( _
   psPath As String _
   , Optional psMask As String = "*.*")
'read files into array and open each one
's4p
   'PARAMETERS
   '  psPath is path to look in
   '  psMask is what to look for (ie: *.jpg)

  Dim psPathFile As String _
     , sFilename As String _
     , i As Integer

   Dim arrFile() As String
   
   psPath = Trim(psPath)
   If Right(psPath, 1) <> "\" Then
      psPath = psPath & "\"
   End If
   
   'first array element will be 0
   i = -1
   sFilename = Dir(psPath & psMask)
   
   'load files matching mask into an array
   Do While sFilename <> ""
      If (GetAttr(psPath & "\" & sFilename) And vbDirectory) <> vbDirectory Then
         i = i + 1
         'redimension array and preserve previous values
         ReDim Preserve arrFile(i)
         'assign filename to array element
         arrFile(i) = sFilename
      End If
       'get next filename
      sFilename = Dir()
   Loop
   
   'open all the files
   If Not UBound(arrFile) >= 0 Then
      'No Files
      Exit Sub
   End If

   'loop through specified files and open
   For i = LBound(arrFile) To UBound(arrFile)
      psPathFile = psPath & arrFile(i)
      ' ----------------------- do whatever you want
   Next i

End Sub

Open in new window

mlcktmguy

ASKER

Exactly what I was looking for, thanks.