Creating a table from files in a folder

J

jpl458

Well-known member
Local time
Yesterday, 18:04
Joined
Mar 30, 2012
Messages
1,198
I have a folder that has data files with dates, and I to get that data into an excel spreadsheet so I can create an ACCESS table. I have found a way to get the file names but cant get into excel but not the dates.

Thanks
 
This is part of a response to a similar question--
post#9 in thread at https://www.accessforums.net/showthread.php?t=85725


You can use a Dir command to get all files of a certain extension and put these into a txt file.
You repeat for different file extensions and append to the txt file.

My example to recurse alldirectories/subdirectories from C:\users\JP and down and identify *.jpg files with path


Uses a Command Prompt
Code:
C:\Users\JP> dir *.jpg /s/b >c:\users\jp\documents\alljpgs.txt
Here is a sample from the alljpgs.txt produced.

C:\Users\JP\AppData\Local\Google\Chrome\User Data\Default\Extensions\fheoggkfdfchfphceeifdbepao oicaho\8.1.0.2126_0\images\crypto\banners\crypto-what-banner.jpg
C:\Users\JP\AppData\Local\Google\Chrome\User Data\Default\Extensions\fheoggkfdfchfphceeifdbepao oicaho\8.1.0.2126_0\images\crypto\banners\protecty ourself-banner.jpg
C:\Users\JP\AppData\Local\Google\Chrome\User Data\Default\Extensions\fheoggkfdfchfphceeifdbepao oicaho\8.1.0.2126_0\images\crypto\banners\server-image-banner.jpg

...You can process the txt file with Access.
 
Not sure what you are doing, but if looping the files in a folder using filesystemobject you can use the File Object which has a dateCreated, dateLastAccessed, and dateLastModified property.

Solved - Access VBA - Loop through files in Folders, Subfolders, Sub-Sub Folders....

Hi, Before I write my own nested nested nested loop, surely someone has something out there already.... I need to list all the files in a single 'base' folder, which includes subfolders of the base folder. and subfolders of the subfolder's subfolders. The nesting of the subfolders is assumed...
www.access-programmers.co.uk
 
MajP said:
Not sure what you are doing, but if looping the files in a folder using filesystemobject you can use the File Object which has a dateCreated, dateLastAccessed, and dateLastModified property.

Solved - Access VBA - Loop through files in Folders, Subfolders, Sub-Sub Folders....

Hi, Before I write my own nested nested nested loop, surely someone has something out there already.... I need to list all the files in a single 'base' folder, which includes subfolders of the base folder. and subfolders of the subfolder's subfolders. The nesting of the subfolders is assumed...
www.access-programmers.co.uk
Click to expand...
I have a folder that has various audio files stored in m4a format, (Created bt the Sound Recorder) and I want to create a list of the filenames and dates created that I can convert to .csv file and load into ACCESS. My problem is I do not understand the file system the the files are stored in. The recorder has a popup menu where you can add fields to be viewed. I can get a list of the names, but I also need the date that are associated with each file.
 
Using a parameter query is also a very good style.
I myself would have used a recordset (single table access).
 
Well, I would use a stored query, that would eliminate the issue of repeatedly creating execution plans. But I also wrote that I would also prefer the recordset, that should be recognizable with two sentences.

But also: The time for creating an execution plan for this super simple query is in the nanosecond range, and not in the high range. What should be optimized here? And yes, I've practically tried to measure pure times for creation before execution plans.

Overall, I am also pleased that a parameter query was used at all instead of assembling the SQL statement using VBA. Readers see that far too seldom. It should be mastered and therefore written off.
 
Last edited:
Pat Hartman said:
@moke123 Thanks for posting an example. Any particular reason you chose to run an append query for each file rather than just opening a recordset and using .AddNew?
Click to expand...
It was meant as a quick and dirty example how it could be done.

I used the QryDef because I like them (no delimiting needed) and I had my CodeGen form loaded which writes the querydef for me, so it only takes seconds. The procedure executes pretty quickly on a local folder of 1000 files.

frmcg.png
 
moke123 said:
Why the csv? You can load directly into the access table.
Click to expand...
Moke, it works like a champ. Always like your answers. Now the challenge for me is to figure out why it works. I don't understand the code at all.

Thanks again
 
I'm not sure what your end goal is but one thing to be careful of is keeping data current if folders and files get added or deleted.

I usually avoid using a table for this type stuff. In my instance, I have a folder for each client and all docs and files, etc. for that client go in that folder. When I open a clients profile form, I run a procedure to list just their files in a list box. I can double click it to open the listed file. The data will always be current if files were added or deleted.

If you want to try the listbox route the relevant code is
Code: 
Public Sub sAddFilesToListBox(TopFold As String, Lbx As ListBox)

    Set fso = New FileSystemObject
    Set fCol = New Collection
 
    fCol.Add TopFold  'add the top folder path to the collection

    sGetFolderCollection TopFold  'call recursive proc to add subfolder paths to collection

    Lbx.rowsource  = ""

    Dim i As Long
    Dim Fldr As Folder, fl As File
    
    For i = 1 To fCol.Count                         'loop through the collection of folder paths

        Set Fldr = fso.GetFolder(fCol(i))

        For Each fl In Fldr.Files       'loop through folder adding file info to table
        
            Lbx.AddItem fl.Name & ";" & fl.Path & ";" & fl.DateCreated & ";" & fl.DateLastModified
 
        Next
      
    Next i

End Sub

It also uses this procedure which is in the example I posted
Code: 
Sub sGetFolderCollection(TopFold As String)

    Dim oTopFolder As Folder
    Dim fol As Folder
    Dim sfol As Folder

    Set oTopFolder = fso.GetFolder(TopFold)

    For Each fol In oTopFolder.SubFolders

        fCol.Add fol.Path

        For Each sfol In fol.SubFolders
            Call sGetFolderCollection(sfol.Path)
        Next

    Next fol

End Sub

You would call it with
Code: 
 sAddFilesToListBox strPath, Me.lstFiles
strPath is the path to your top folder, and replace lstFiles with the name of your value list listbox.
 
moke123 said:
I'm not sure what your end goal is but one thing to be careful of is keeping data current if folders and files get added or deleted.

I usually avoid using a table for this type stuff. In my instance, I have a folder for each client and all docs and files, etc. for that client go in that folder. When I open a clients profile form, I run a procedure to list just their files in a list box. I can double click it to open the listed file. The data will always be current if files were added or deleted.

If you want to try the listbox route the relevant code is
Code: 
Public Sub sAddFilesToListBox(TopFold As String, Lbx As ListBox)

    Set fso = New FileSystemObject
    Set fCol = New Collection

    fCol.Add TopFold  'add the top folder path to the collection

    sGetFolderCollection TopFold  'call recursive proc to add subfolder paths to collection

    Lbx.rowsource  = ""

    Dim i As Long
    Dim Fldr As Folder, fl As File
   
    For i = 1 To fCol.Count                         'loop through the collection of folder paths

        Set Fldr = fso.GetFolder(fCol(i))

        For Each fl In Fldr.Files       'loop through folder adding file info to table
       
            Lbx.AddItem fl.Name & ";" & fl.Path & ";" & fl.DateCreated & ";" & fl.DateLastModified

        Next
     
    Next i

End Sub

It also uses this procedure which is in the example I posted
Code: 
Sub sGetFolderCollection(TopFold As String)

    Dim oTopFolder As Folder
    Dim fol As Folder
    Dim sfol As Folder

    Set oTopFolder = fso.GetFolder(TopFold)

    For Each fol In oTopFolder.SubFolders

        fCol.Add fol.Path

        For Each sfol In fol.SubFolders
            Call sGetFolderCollection(sfol.Path)
        Next

    Next fol

End Sub

You would call it with
Code: 
 sAddFilesToListBox strPath, Me.lstFiles
strPath is the path to your top folder, and replace lstFiles with the name of your value list listbox.
Click to expand...
Moke, I have been using the example you sent, but the code is a bit above my experience level with VBA. But, your example clarified my thinking about what I was trying to do. I use the Voice Recorder to record various calls, conversations,etc. Voice Recorder stores those recordings in Documents\Sound Recordings. I know the path to the files, so I don't need to enter it each time. I need some help with code that would reside behind a button that would get the latest recordings and their properties; those files that are later than the last date in a table in an ACCESS database. There is other information that needs to be added to the table, which can only be done by hand, but if I can get the recording name and the start and modified time I can reduce what has to be entered by hand. The flow would look like this:

Get Voice Recording data later than the newest recording date in the table
Append that data to the data in the table
Fill in the blanks at a later time from notes, or listening to the recording.

I hope this makes sense, and I really appreciate the time and the examples. I keep all you examples in the Moke folder.

Thanks again
 
What properties? "the latest recordings and their properties;"
 
I gave you 2 options, Save to table and list in a list box. Which one are you using?
jpl458 said:
a button that would get the latest recordings and their properties; those files that are later than the last date in a table in an ACCESS
Click to expand...
If listing to a table, do you want to get only the new files, or just the latest? How many files to you anticipate having?
 
moke123 said:
I gave you 2 options, Save to table and list in a list box. Which one are you using?

If listing to a table, do you want to get only the new files, or just the latest? How many files to you anticipate having?
Click to expand...

moke123 said:
I gave you 2 options, Save to table and list in a list box. Which one are you using?

If listing to a table, do you want to get only the new files, or just the latest? How many files to you anticipate h
Click to expand...

moke123 said:
I gave you 2 options, Save to table and list in a list box. Which one are you using?

If listing to a table, do you want to get only the new files, or just the latest? How many files to you anticipate having?
Click to expand...
There is no real volume, maybe 5 or 6 recordings max in a day, but mostly less. I am currently using your example as a utility external to ACCESS. I run your example , then export Table1 from your example to my ACCESS app,(after deleting the previous one and saving the max datetime). I then can create what I need with queries. I create a query that then gets all the files that are newer on the new Table1 than the max date from the previous table1. I want to automate that process, but I am having a difficult time understanding your code. I've been running it over and over with a lot debug.prints to try and understand the part that I need. I will continue to work on it. But, some of the terms in your code I can't find anywhere on the web..

Thanks again
 
It appears to me that the example you sent is like a utility that can be used on a lot of folders containing PDF, pictures and audio files.
The first sections deal with setting up the filepicker and getting the folder to be examined. For my proposes I need to get the file name and properties from a specific folder, every time, and that folder contains audio files in .m4a format. I have copied parts of the example that I think have what I am looking for. (Correct me if I am wrong.) I am guessing that the part I need to use is the last section of code below:
Sub AddFilesToTable. The Last line of the top bit of code calls/branches to AddFilesToTable routine. (Is that correct?) and that's the routine that builds the table. I need to know if that last section can stand alone, and if not, what do I have to change to make it so?

Lastly, for now, I would like to constrain the output to only files that are newer than a date that I get from my app. I'm pretty sure I can figure that out if I can get the part I need to work.

Thanks so much.
----------------------------------------------------
Code: 
Sub ProcessListFiles(TFolder As String)

    Set fso = New FileSystemObject
    Set fCol = New Collection

    fCol.Add TFolder  'add the top folder path to the collection
      
    sGetFolderCollection TFolder  'call recursive proc to add subfolder paths to collection

   AddFilesToTable (Branches to the subroutine, Correct?)

----------------------------------------------

Sub sGetFolderCollection(TopFold As String)

    Dim oTopFolder As Folder
    Dim fol As Folder
    Dim sfol As Folder

    Set oTopFolder = fso.GetFolder(TopFold)
Debug.Print oTopFolder
    For Each fol In oTopFolder.SubFolders

        fCol.Add fol.Path

        For Each sfol In fol.SubFolders
            Call sGetFolderCollection(sfol.Path)
        Next

    Next fol

End Sub
-----------------------------------------

Sub AddFilesToTable()

    Dim i As Integer
    Dim Fldr As Folder, fl As File
    For i = 1 To fCol.Count                         'loop through the collection of folder paths

        Set Fldr = fso.GetFolder(fCol(i))
Debug.Print Fldr
        For Each fl In Fldr.Files       'loop through folder adding file info to table

            Const QDef_Insert As String = _
                "Insert into Table1" & _
                "(FName,FPath,dteCreated,dteModified,FSize,fBaseName,FExt,PFolder) " & _
                "Values(p0,p1,p2,p3,p4,p5,p6,p7)"

            With CurrentDb.CreateQueryDef("", QDef_Insert)
                .Parameters(0) = fl.Name
                .Parameters(1) = fl.Path
                .Parameters(2) = fl.DateCreated
                .Parameters(3) = fl.DateLastModified
                .Parameters(4) = fl.Size / 1000000 & "Mb"
                .Parameters(5) = fso.GetBaseName(fl.Path)
                .Parameters(6) = fso.GetExtensionName(fl.Path)
                .Parameters(7) = fso.GetParentFolderName(fl.Path)
                .Execute dbFailOnError
                .Close
            End With
        Next
    Next i
 
Last edited:
Here is the start of my attempt to cannibalize your code. The following is in the OnClick event of a button, but I get this error:

1672352941953.png


On the
Code: 
Dim Fldr As Folder, fl As File

Code: 
Private Sub GetCallsbtn_Click()
'Mokes Code Get File names and properties and put in Table1
   'Dim i As Integer
    Dim Fldr As Folder, fl As File
    'Dim f1 As File
    'For i = 1 To fCol.Count                         'loop through the collection of folder paths

         Fldr = "c:\Users\me\OneDrive\Documents\Sound recordings"                                       'fso.GetFolder(fCol(i))
Debug.Print Fldr
        For Each fl In Fldr.Files       'loop through folder adding file info to table

            Const QDef_Insert As String = _
                "Insert into Table1" & _
                "(FName,FPath,dteCreated,dteModified,FSize,fBaseName,FExt,PFolder) " & _
                "Values(p0,p1,p2,p3,p4,p5,p6,p7)"

            With CurrentDb.CreateQueryDef("", QDef_Insert)
                .Parameters(0) = fl.Name
                .Parameters(1) = fl.Path
                .Parameters(2) = fl.DateCreated
                .Parameters(3) = fl.DateLastModified
                .Parameters(4) = fl.Size / 1000000 & "Mb"
                .Parameters(5) = fso.GetBaseName(fl.Path)
                .Parameters(6) = fso.GetExtensionName(fl.Path)
                .Parameters(7) = fso.GetParentFolderName(fl.Path)
                .Execute dbFailOnError
                .Close
            End With
Debug.Print i
        Next
Debug.Print i
    'Next i

End Sub

I have searched all of the code in the example and can't find where you define Folder, if that is what's causing this error. After reading through this so many time, it's becoming clearer. What is also clear is how little I know about this part of VBA. Any edits would be greatly appreciated.

Thanks again
 
Last edited:
jpl458 said:
It appears to me that the example you sent is like a utility that can be used on a lot of folders containing PDF, pictures and audio files.
The first sections deal with setting up the filepicker and getting the folder to be examined.
Click to expand...
Pretty much. I tend to write code in pieces especially when posting examples to simplify them. In this case it was easier to just get a collection of all the folders and subfolders and then use them to get all the files in them. It could be written in one procedure but why complicate it.

I take it your new to using FSO? Here's a few links which I find helpful
https://analystcave.com/vba-filesystemobject-fso-in-excel/
https://www.virtualsplat.com/tips/visual-basic-fso.asp
https://learn.microsoft.com/en-us/o...e/user-interface-help/filesystemobject-object

jpl458 said:
For my proposes I need to get the file name and properties from a specific folder, every time, and that folder contains audio files in .m4a format.
Click to expand...
If it's just one folder with no subfolders its pretty easy.
Code: 
Sub AddFilesToTable()

    Dim Fldr As Folder, fl As File
    
        Set Fldr = fso.GetFolder("c:\Users\me\OneDrive\Documents\Sound recordings")

        For Each fl In Fldr.Files       'loop through folder adding file info to table

' You could add criteria here such as
' If fso.GetExtensionName(fl.Path) = "m4a" then . . . 
'or  If fl.DateCreated > Some Date ...
' or if using a dictionary to limit it to new files
' If not dict.exists(fl.Path) then ...

            Const QDef_Insert As String = _
                "Insert into Table1" & _
                "(FName,FPath,dteCreated,dteModified,FSize,fBaseName,FExt,PFolder) " & _
                "Values(p0,p1,p2,p3,p4,p5,p6,p7)"

            With CurrentDb.CreateQueryDef("", QDef_Insert)
                .Parameters(0) = fl.Name
                .Parameters(1) = fl.Path
                .Parameters(2) = fl.DateCreated
                .Parameters(3) = fl.DateLastModified
                .Parameters(4) = fl.Size / 1000000 & "Mb"
                .Parameters(5) = fso.GetBaseName(fl.Path)
                .Parameters(6) = fso.GetExtensionName(fl.Path)
                .Parameters(7) = fso.GetParentFolderName(fl.Path)
                .Execute dbFailOnError
                .Close
            End With

        Next

        set fso = nothing
  
End Sub

jpl458 said:
Lastly, for now, I would like to constrain the output to only files that are newer than a date that I get from my app. I'm pretty sure I can figure that out if I can get the part I need to work.
Click to expand...
Is this so you only get files that have not been added to the table yet?
What I do is create a dictionary object and add all the file paths in the table. Scripting.Dictionaries have an exists method which makes it simple to filter out files that have already been added.

Here's an example with the dictionary
 

Attachments

You must log in or register to reply here.

Similar threads

S
windows 11, cant see hidden files in folder
2
Replies
29
Views
1,181
The_Doc_Man
The_Doc_Man
E
BAT File Command Help
2
Replies
30
Views
2,537
Jason Lee Hayes
Jason Lee Hayes
S
Batch file for updating local frontend Access database copy
2
Replies
26
Views
3,872
arnelgp
arnelgp
R
Importing a spreadsheet to existing table where column names don't match
Replies
15
Views
1,262
gemma-the-husky
gemma-the-husky
T
Back End Read-Only Issue
2
Replies
26
Views
2,031
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom