Recursive file and folder audit...

A few years ago, Rey Obrero (Microsoft Access MVP) gave me a solution for this question. But now, I am not able to locate it. Suppose I specify a drive name or a folder name like:

AuditFiles("c:")  or Audit("C:\MyData")

Staying with drive example for now, if we have the following files and folders:

             c.xls  <-- this file is in Folder_1

There could be many levels of subfolders. I am trying to read file names and folder names to enter in tblFIleAndFolders like:

ID_PK           Name               ID_FK            FileYN
     1                C:                      null
     2          Folder_1                  1    <-- meaning it is in C:
     3          c.xls                         2                    Yes
     4          FolderA                   2
     5          a.xls                        4                    Yes
     6          b.xls                        4                    Yes
     7         Folder_2                  1
     8          d.xls                        7                    Yes
     9          e.xls                        7                    Yes

Question: Could this be done recursively to include all .xls files, folders, and subfolders (regardless it has .xls or not)?

Order of entries is not important. I will, at some point, expand this to include .doc, .png, etc.
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I don't know that I'd work that hard.
You could shell out dir /o/b/s > somefile.csv from the starting point.
You then have a textfile.
Load it up into a TextStream object
Walk down it.
Throw each line into a Split(TheLine,"\")
If the last element isn't like *.xls, move on
If it is, walk down the split.
Check each element.
Is it already in the table?  No? Enter it.
The second last element will be the folder the file is in.
Create the entry for the file.

Make sense?
What help would you need to implement it?
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
BTW, I will welcome SQL Server solution with Procs etc. I have requested SQL Server TA to be added to this question.

Here is sample code I am trying to revise to do the job:
Option Compare Database
Option Explicit

'list files to tables

Dim gCount As Long ' added by Crystal
Sub audit()
Call ListFilesToTable("C:", "*.zip")
End Sub
Sub runListFiles()
    'Usage example.
    Dim strPath As String _
    , strFileSpec As String _
    , booIncludeSubfolders As Boolean
    strPath = "E:\"
    strFileSpec = "*.*"
    booIncludeSubfolders = True
    ListFilesToTable strPath, strFileSpec, booIncludeSubfolders
End Sub

'crystal modified parameter specification for strFileSpec by adding default value
Public Function ListFilesToTable(strPath As String _
    , Optional strFileSpec As String = "*.*" _
    , Optional bIncludeSubfolders As Boolean _
On Error GoTo Err_Handler
    'Purpose:   List the files in the path.
    'Arguments: strPath = the path to search.
    '           strFileSpec = "*.*" unless you specify differently.
    '           bIncludeSubfolders: If True, returns results from subdirectories of strPath as well.
    'Method:    FilDir() adds items to a collection, calling itself recursively for subfolders.
    Dim colDirList As New Collection
    Dim varitem As Variant
    Dim rst As DAO.Recordset
   Dim mStartTime As Date _
      , mSeconds As Long _
      , mMin As Long _
      , mMsg As String
   mStartTime = Now()
    Call FillDirToTable(colDirList, strPath, strFileSpec, bIncludeSubfolders)
   mSeconds = DateDiff("s", mStartTime, Now())
   mMin = mSeconds \ 60
   If mMin > 0 Then
      mMsg = mMin & " min "
      mSeconds = mSeconds - (mMin * 60)
      mMsg = ""
   End If
   mMsg = mMsg & mSeconds & " seconds"
   MsgBox "Done adding " & Format(gCount, "#,##0") & " files from " & strPath _
      & IIf(Len(Trim(strFileSpec)) > 0, " for file specification --> " & strFileSpec, "") _
      & vbCrLf & vbCrLf & mMsg, , "Done"
   SysCmd acSysCmdClearStatus
    Exit Function

    MsgBox "Error " & Err.Number & ": " & Err.Description, , "ERROR"
    'remove next line after debugged -- added by Crystal
    Stop: Resume 'added by Crystal
    Resume Exit_Handler
End Function

Private Function FillDirToTable(colDirList As Collection _
    , ByVal strFolder As String _
    , strFileSpec As String _
    , bIncludeSubfolders As Boolean)
    'Build up a list of files, and then add add to this list, any additional folders
    On Error GoTo Err_Handler
    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant
    Dim strSQL As String

    'Add the files to the folder.
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> vbNullString
         gCount = gCount + 1
         SysCmd acSysCmdSetStatus, gCount
         strSQL = "INSERT INTO Files " _
          & " (FName, FPath) " _
          & " SELECT """ & strTemp & """" _
          & ", """ & strFolder & """;"
         CurrentDb.Execute strSQL
        colDirList.Add strFolder & strTemp
        strTemp = Dir

    If bIncludeSubfolders Then
        'Build collection of additional subfolders.
        strTemp = Dir(strFolder, vbDirectory)
        Do While strTemp <> vbNullString
            If (strTemp <> ".") And (strTemp <> "..") Then
                If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
                    colFolders.Add strTemp
                End If
            End If
            strTemp = Dir
        'Call function recursively for each subfolder.
        For Each vFolderName In colFolders
            Call FillDirToTable(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
        Next vFolderName
    End If

    Exit Function

    strSQL = "INSERT INTO Files " _
    & " (FName, FPath) " _
    & " SELECT ""  ~~~ ERROR ~~~""" _
    & ", """ & strFolder & """;"
    CurrentDb.Execute strSQL
    Resume Exit_Handler
End Function

Public Function TrailingSlash(varIn As Variant) As String
    If Len(varIn) > 0& Then
        If Right(varIn, 1&) = "\" Then
            TrailingSlash = varIn
            TrailingSlash = varIn & "\"
        End If
    End If
End Function

Open in new window

What you have posted is not at all an attempt to create what I have suggested, which would work simply and admitably well.  Which parts of that solution  -- piping out cmd /c dir  > /o/b/s ; opening the result as a TextStream object ; reading each line into a Split() array ;  or the loop to process the result -- is giving you grief?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Mike EghtebasDatabase and Application DeveloperAuthor Commented:

I think I will delete this question from Access and post it in SQL Server. See: for a very close solution.

Since you seem to be reluctant to build it, I'll start.
Here's code to build and execute a batchfile to create a flat listing of the path selected
Dim db As Database
Dim rs As Recordset
Dim batfile As Object
Dim fs As Object

Set fs = CreateObject("Scripting.FileSystemObject")
Set batfile = fs.CreateTextFile("c:\temp\dir.bat", True)
'ok lets build the batchfile to do the dir
'go to the correct drive
batfile.WriteLine (Left(Me.txtPath, 2) & "\") 'probably c:\
batfile.WriteLine ("cd\") 'back to the root
batfile.WriteLine ("cd " & Me.txtPath) ' to the path
'Ok let's pipe it out
batfile.WriteLine ("dir *.* /o/b/s > c:\temp\dir.txt")
batfile.WriteLine ("pause")

'ok, execute it
Shell "cmd /c c:\temp\dir.bat"
MsgBox "it begins"

Open in new window

Next up, opening the newly created listing and beginning the parsing
This code will grab a dir and stuff it into a table of paths and filenames.
Dim db As Database
Dim rs As Recordset

Dim batfile As Object
Dim dirfile As Object
Dim fs As Object
Dim textline As String
Dim myDictionary As Object
Dim SlashSplit() As String
Dim ThePath As String
Dim TheFilename As String

Set fs = CreateObject("Scripting.FileSystemObject")
Set batfile = fs.CreateTextFile("c:\temp\dir.bat", True)
'ok lets build the batchfile to do the dir
'go to the correct drive
batfile.WriteLine (Left(Me.txtPath, 2)) 'probably c:\
batfile.WriteLine ("cd\") 'back to the root
batfile.WriteLine ("cd " & Me.txtPath) ' to the path
'Ok let's pipe it out
batfile.WriteLine ("dir *." & IIf(Len(Nz(Me.txtExtension.Value, "*")) <> 3, "*", Me.txtExtension.Value) & " /o/b/s>c:\temp\dir.txt")
batfile.WriteLine ("pause")

'ok, execute it
Shell "cmd /c c:\temp\dir.bat"
MsgBox "Click OK only after closing the batch file"

'clean up the batch file
fs.DeleteFile "c:\temp\dir.bat", True

'start up the dictionary
'Set myDictionary = CreateObject("Scripting.Dictionary")

'get the recordset ready
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from tblPaths where 1=2;", dbOpenDynaset, dbSeeChanges)
'open the file listing
Set dirfile = fs.OpenTextFile("c:\temp\dir.txt", ForReading)
' Read from the file and display the results.
Do While dirfile.AtEndOfStream = False
    textline = dirfile.ReadLine
    'alright let's split the textline
    SlashSplit = Split(textline, "\")
    'Ok, the last item is the filename
    'everything else is the path
    With rs
        !FileName = SlashSplit(UBound(SlashSplit))
        ThePath = Left(textline, Len(textline) - Len(SlashSplit(UBound(SlashSplit))))
        !Path = ThePath
    End With
MsgBox "Processed to Paths & Filenames"

'Now the fun begins

Open in new window

How do you want to recurse and store things like
Hi Nick & eghtbas,

I have done such an application (liitle bit more elaborate) already for image-files. If you are interested i will strip it down to just look for Excel files. As it will take me some time to do it, i ask you first, if you are interested.
Mike EghtebasDatabase and Application DeveloperAuthor Commented:

I appreciate that. I look forward to it.

Hi Mike,

I attach a test-db which is a stripped version of a little image administration tool.
Please start with the drive, then proceed with the directories and as 3rd step the files.
The application is time consuming, so let it work in the background, if you test it on a machine with a lot of diretories.
It recursively walks trough all directores and files, so it can be a base for various purpose.
I hope it gives you some ideas for your specific rquirements.
The file information is comparing data from previous scans, so it will indicate also missing files versus last scan.
Please be aware, that the CreateDate is the Date when a file was stored at the current location. The last Changed date may be older, if a file was just copied as it is.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.