Results 1 to 12 of 12
  1. #1
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    51

    Add File Date Created and Modified to File List

    Greetings,

    I'm using Allen Browne's code (added to this post) to create a list of file names and directory paths for a specific directory and its sub-directories. It works great. But I also want to pull in the file date created and file date modified to this list. Can someone tell me how to add DateCreated and DateModifed to the code?
    Cheers.


    Option Compare Database
    Option Explicit


    'list files to tables
    'http://allenbrowne.com/ser-59alt.html


    Dim gCount As Long ' added by Crystal


    Sub runListFiles()
    'Usage example.
    Dim strPath As String _
    , strFileSpec As String _
    , booIncludeSubfolders As Boolean

    strPath = "C:\Program Files\Common Files"
    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)
    Else
    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"

    Exit_Handler:
    SysCmd acSysCmdClearStatus
    '--------

    Exit Function


    Err_Handler:
    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
    Loop


    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
    Loop
    'Call function recursively for each subfolder.
    For Each vFolderName In colFolders
    Call FillDirToTable(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
    Next vFolderName
    End If


    Exit_Handler:

    Exit Function


    Err_Handler:
    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
    Else
    TrailingSlash = varIn & ""
    End If
    End If
    End Function

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    51
    Quote Originally Posted by Welshgasman View Post
    I'm not sure I understand your response Welshgasman. Was it meant to belittle me?

    I'm looking for help to modify my code I posted. How can your suggestion be integrated into my code (correction, Allen's code)?

    I was not aware that VB used in Excel can be utilized in Access.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    No, it was meant to indicate that a simple search can provide the code you need to get the properties that you require.
    Other properties like size can be obtained in the same way?
    Most Vba has common code that can be used in all apps.
    However Access would not recognise a excel sheet or word document, without using the respective object.
    In fact the function used in that link is pure vba,not tied to any particular app.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    51
    I'm still fairly new to VB and still learning. Will you please show me how to integrate this into the code?

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    @isladogs also has a DB that demonstrates this process.

    https://www.access-programmers.co.uk.../#post-1783069
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    51
    Thank you for your time Welshgasman.

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    That first link I posted has the command to get the created date. To do that it needs the full path of the file, which you already have.
    So when you have that filename use that function to get the required properties. After that you do what you want with them.
    Look at isladogs db, to see how he does it. You can always learn a lot from Colin's code.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    51
    Thank you for your time Welshgasman.

  10. #10
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    51
    I'm reaching out to other forum members. I truly do not know how to integrate the Welshgasman's suggestion links into Allen's code. I need a code that will look in a folder and it's sub folders and list all the file names, directory, create and modify date. Allen's code does most of that except providing the create and modify date. I'm still new to VB. Can someone please help me?
    I did a Google search to find Allen's code and thought I could get additional help here. But instead I received a snarky reply
    ("I have Google on this laptop") with links to codes I don't know how to use. I work for one of the big cellular companies as a tech support leader. And like yourselves, I donate my time on forums such as this to help others. If you need my help, I will not give you snarky replies and links to other sites that may or may not help you. I will work with you until you issue is resolved.


  11. #11
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    This is similiar to Alan's but uses fso instead of Dir()
    It includes File name, path, size, date created and modified, Base Name, extension, and parent folder.
    Attached Files Attached Files
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  12. #12
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    51
    Thank you moke123. This is perfect! Cheers

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-13-2020, 08:56 PM
  2. Date Created and Date last modified
    By Detectiveclem in forum Access
    Replies: 6
    Last Post: 08-29-2018, 10:15 AM
  3. Replies: 15
    Last Post: 12-07-2014, 06:22 AM
  4. Importing file directly from a Sharepoint file (not list)
    By jstoler in forum Import/Export Data
    Replies: 1
    Last Post: 06-28-2013, 01:44 PM
  5. Replies: 5
    Last Post: 12-18-2012, 02:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums