Find all file extensions in folder and sub folders and save to access table

I have an access table tblExtensions with one filed Extension.
Is there a way to search a folder and all sub folders (i.e. C:\Users) and return the file extensions of every file to the tblExtensions table?

I am trying to build a list of file types in a diretory.

Thank you in advance.

Steve
LVL 1
ScamquistAsked:
Who is Participating?
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.

fundacionrtsAdministrador de SistemasCommented:
Dim objFileSystem

Sub insertExtension(strExtension)
      ojConnection.Execute "INSERT INTO tblExtensions(Extension) VALUES (""" & strExtension & """)"
End Sub

Function subfolders(strFolder)
      Dim objCurrent, objFolder, objFile
      Set objCurrent = objFileSystem.GetFolder(strFolder)
      
      For Each objFile In objCurrent.Files
            insertExtension objFileSystem.GetExtensionName(objFile.Name)
      Next
      For Each objFolder In objCurrent.Subfolders
            WScript.Echo objFolder.Path
            subfolders objFolder.Path
      Next
End Function

Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Set ojConnection = CreateObject("ADODB.Connection")
ojConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\path\to\database.mdb"

subfolders "c:\path\to\folder\"

ojConnection.Close

Set ojConnection = Nothing
Set objFileSystem = Nothing
0
ScamquistAuthor Commented:
I am confused.  How do I incorporate this into my database?
0
fundacionrtsAdministrador de SistemasCommented:
Ups, excuse me! I understood that you need a "external" script and not a MS Access script! (my English is not very good). Sorry!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
Check this.

Make a table named: tblExtensions
Fields:
ID (AutoNumber)
Extension (Text, 5 characters)

Make a table called: tblUniqueExtensions
Fields:
Extension (Text, 5 characters)

(Disclaimer: Original code modified form here:
http://www.ammara.com/access_image_faq/recursive_folder_search.html)

Put this code on a button on a form:

Dim colFiles As New Collection
    RecursiveDir colFiles, "C:\YourFolder", "*.*", True
'    CurrentDb.Execute "DROP TABLE tblUniqueExtensions", dbFailOnError
   
    CurrentDb.Execute "DELETE * FROM tblExtensions", dbFailOnError

    Dim vFile As Variant
    For Each vFile In colFiles
        Debug.Print vFile
        CurrentDb.Execute "INSERT INTO tblExtensions (Extension) VALUES(" & "'" & Right(vFile, 3) & "'" & ")", dbFailOnError
    Next vFile
   
    CurrentDb.Execute "SELECT tblExtensions.Extension INTO tblUniqueExtensions FROM tblExtensions GROUP BY tblExtensions.Extension", dbFailOnError





Put this code in a Module:

Public Function RecursiveDir(colFiles As Collection, strFolder As String, strFileSpec As String, bIncludeSubfolders As Boolean)

    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant

    'Add files in strFolder matching strFileSpec to colFiles
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> vbNullString
        colFiles.Add strFolder & strTemp
        strTemp = Dir
    Loop

    If bIncludeSubfolders Then
        'Fill colFolders with list of subdirectories of strFolder
        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
        Loop

        'Call RecursiveDir for each subfolder in colFolders
        For Each vFolderName In colFolders
            Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
        Next vFolderName
    End If

End Function

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



This worked fine for me.
The File Upload function here is not working right now.

;-)

JeffCoachman
0

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
Jeffrey CoachmanMIS LiasonCommented:
Actually this is the correct code for the button...

Dim colFiles As New Collection
    RecursiveDir colFiles, "C:\YourFolder", "*.*", True
    CurrentDb.Execute "DROP TABLE tblUniqueExtensions", dbFailOnError
   
    CurrentDb.Execute "DELETE * FROM tblExtensions", dbFailOnError

    Dim vFile As Variant
    For Each vFile In colFiles
        Debug.Print vFile
        CurrentDb.Execute "INSERT INTO tblExtensions (Extension) VALUES(" & "'" & Right(vFile, 3) & "'" & ")", dbFailOnError
    Next vFile
   
    CurrentDb.Execute "SELECT tblExtensions.Extension INTO tblUniqueExtensions FROM tblExtensions GROUP BY tblExtensions.Extension", dbFailOnError
0
Jeffrey CoachmanMIS LiasonCommented:
...More refinements.

The above code will only get the last three characters of the extension
(This will truncate many of the newer/longer file extensions like .accdb, and .HTML)
This code takes the last 5 characters:

Dim colFiles As New Collection
    RecursiveDir colFiles, "C:\YourFolder", "*.*", True
    CurrentDb.Execute "DROP TABLE tblUniqueExtensions", dbFailOnError
   
    CurrentDb.Execute "DELETE * FROM tblExtensions", dbFailOnError

    Dim vFile As Variant
    For Each vFile In colFiles
        Debug.Print vFile
        CurrentDb.Execute "INSERT INTO tblExtensions (Extension) VALUES(" & "'" & Right(vFile, Len(vFile) - InStr(vFile, ".")) & "'" & ")", dbFailOnError
    Next vFile
   
    CurrentDb.Execute "SELECT tblExtensions.Extension INTO tblUniqueExtensions FROM tblExtensions GROUP BY tblExtensions.Extension", dbFailOnError
0
ScamquistAuthor Commented:
I added my parent folder location.
When I run the code I get the error

Run-time error '52':      
Bad file name or number

Debug stops on the last line below.

Public Function RecursiveDir(colFiles As Collection, strFolder As String, strFileSpec As String, bIncludeSubfolders As Boolean)

    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant

    'Add files in strFolder matching strFileSpec to colFiles
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
0
Jeffrey CoachmanMIS LiasonCommented:
Here is the sample db that worked fine for me...
Access-EEQ27640954Recursion--Rec.mdb
0
ScamquistAuthor Commented:
Jeff,
I have not found the difference in my code and yours, but the database you provided worked.  I was doing a similar recursive search to find files.  I just couldn't get the extension only part.

Thank you for the assist.
0
Jeffrey CoachmanMIS LiasonCommented:
Great!

As you can see, I simply modified the code from:
http://www.ammara.com/access_image_faq/recursive_folder_search.html

So I could not simply post the code here without giving the proper credit...

;-)

Jeff
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.