Scamquist
asked on
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
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
ASKER
I am confused. How do I incorporate this into my database?
Ups, excuse me! I understood that you need a "external" script and not a MS Access script! (my English is not very good). Sorry!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
...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
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
ASKER
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)
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)
Here is the sample db that worked fine for me...
Access-EEQ27640954Recursion--Rec.mdb
Access-EEQ27640954Recursion--Rec.mdb
ASKER
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.
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.
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
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
Sub insertExtension(strExtensi
ojConnection.Execute "INSERT INTO tblExtensions(Extension) VALUES (""" & strExtension & """)"
End Sub
Function subfolders(strFolder)
Dim objCurrent, objFolder, objFile
Set objCurrent = objFileSystem.GetFolder(st
For Each objFile In objCurrent.Files
insertExtension objFileSystem.GetExtension
Next
For Each objFolder In objCurrent.Subfolders
WScript.Echo objFolder.Path
subfolders objFolder.Path
Next
End Function
Set objFileSystem = CreateObject("Scripting.Fi
Set ojConnection = CreateObject("ADODB.Connec
ojConnection.Open "Provider=Microsoft.Jet.OL
subfolders "c:\path\to\folder\"
ojConnection.Close
Set ojConnection = Nothing
Set objFileSystem = Nothing