Results 1 to 15 of 15
  1. #1
    aquabp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    69

    Pull Dir Files to Table and include File Size and File Creation Date

    I am creating a db file cabinet for project plans and project pictures. I found code from Allen Bowne that works pretty good. Code is lengthy so here is the link to it if needed: http://allenbrowne.com/ser-59.html This code pulls in the FName and FPath as well as an ID and date Uploaded into a table called files.



    I would like to figure out how to pull in the file size and file creation date when pulling new files into the table for comparison purposes.
    - His code is a little out of my league.

    If I need to add the code please let me know.

    Thanks in advance!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Do a test of this code:

    Code:
    Public Sub GetFileProperties()
    Dim sFile As Variant
    Dim oShell: Set oShell = CreateObject("Shell.Application")
    Dim oDir:   Set oDir = oShell.Namespace("C:\")
    For Each sFile In oDir.Items
       Debug.Print oDir.GetDetailsOf(sFile, 0)
    Next
    End Sub
    Here is info about the GetDetailsOf method: https://msdn.microsoft.com/en-us/lib...or=-2147217396
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    aquabp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    69
    Thanks!! I'll work on this and post if I run into issues.

  4. #4
    aquabp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    69
    Looking at the info in the link it looks like this method pulls info for a particular file not a group of files. I found some code examples of this but could not get them to work. The shell variable came back as a user defined error. In researching this I came across the code below. My cat stepped on the key board at this point so I do not know who to give credit to.

    This looks to have all the items I am looking for. Ok so this is way way above my skill level. Sorry. Dumb question time.
    I put it in a module and tried to call it from a from button. Got a compiler error that sub or function was not defined.
    I then put it in the button and nothing happened.
    I have created a table called filename with Attributes, Date Created, Date last Accessed, Date Last Modified, Drive, Path, and Size.

    So will this code work, and if so how does the code load the table?

    Code:
    On Error GoTo err_fInfo
     Dim myFSO, myFileObject
    
    
     Dim lngAttributes As Long 'Attribute Values, explanation below
     Dim datCreated As Date 'Date and time the file was created.
     Dim datLastAccessed As Date 'Date and time the file was last accessed.
     Dim datLastModified As Date 'Date and time the file was last modified.
     Dim strDrive As String 'Drive letter of the drive where the file resides.
     Dim strName As String 'Sets or returns the name of the file.
     Dim strPath As String 'The path of the file, including drive letter.
     Dim strSize As String 'The size, in bytes, of the file.
    
    
     '*)Component values of the Attribute property. Attribute Value Description Access
     'Normal 0 Normal file; no attributes are set
     'ReadOnly 1 Read-only file Read/write
     'Hidden 2 Hidden file Read/write
     'System 4 System file Read/write
     'Volume 8 Disk drive volume label Read-only
     'Directory 16 Folder or directory Read-only
     'Archive 32 File has changed since last backup Read/write
     'Alias 64 Link or shortcut Read-only
     'Compressed 128 Compressed file Read-only
    
    
     Set myFSO = CreateObject("Scripting.FileSystemObject")
     Set myFileObject = myFSO.GetFile(FileName)
    
    
     If fReturn = "" Then
     With myFileObject
     lngAttributes = .Attributes
     datCreated = .DateCreated
     datLastAccessed = .dateLastAccessed
     datLastModified = .dateLastModified
     strDrive = .Drive
     strName = .Name
     strPath = .Path
     strSize = .Size
     End With
     ' return all file info as string
     GetFileInfo = "File Information for file:" & vbCrLf & vbCrLf & _
     "Filename: " & strName & vbCrLf & _
     "Attributes: " & lngAttributes & vbCrLf & _
     "Date Created: " & datCreated & vbCrLf & _
     "Date last Accessed: " & datLastAccessed & vbCrLf & _
     "Date Last Modified: " & datLastModified & vbCrLf & _
     "Drive: " & strDrive & vbCrLf & _
     "Path: " & strPath & vbCrLf & _
     "Size: " & strSize & " Bytes"
     Else
     Dim vReturn As Variant
     Select Case fReturn
     Case "DateCreated"
     vReturn = myFileObject.DateCreated
     Case "DateLastMod"
     vReturn = myFileObject.dateLastModified
     Case "DateLastAccessed"
     vReturn = myFileObject.dateLastAccessed
     Case "Size"
     vReturn = myFileObject.Size & " Bytes"
     Case Else
     vReturn = myFileObject.Name
     End Select
     GetFileInfo = vReturn
     End If
    
    
    Exit_err:
     Set myFileObject = Nothing
     Set myFSO = Nothing
     Exit Function
    err_fInfo:
     GetFileInfo = Err.Description
     Resume Exit_err

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That code does not show Sub or Function declaration line.

    What do you mean by 'put it in the button'? Did you select [Event Procedure] in button click event? Did you click the ellipsis (...) to open VBA editor? Did you put code in the procedure?

    Yes, the code will retrieve file info but nothing in that code will save data to table. Would need something like:

    CurrentDb.Execute "INSERT INTO tablename (FileName, DateCreated) VALUES('" & FileName & "',#" & .DateCreated & "#)"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Here is a link to the code posted in Post #4
    http://www.utteraccess.com/forum/vba...-t1304431.html

    It is a Public Function...

  7. #7
    aquabp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    69
    Yes I pasted this code in a button click event after I tried putting it in a module and calling it from the button click event. Leaving it in the button click event I pasted your line in the code.

    CurrentDb.Execute "INSERT INTO tablename (FileName, DateCreated) VALUES('" & FileName & "',#" & .DateCreated & "#)"

    Note: I changed tablename to files since this file was created already. Checked to make sure fields matched.

    At first I put before the end function statement but the error said that the .DateCreated phrase needed to be inside a with statement so I moved it there. No luck in either case. I am checking the code I currently use to pull file names into this table to see what I am missing with this code. Also seeing if the code I am currently using can be updated to include the file attributes I am looking for, file Size, file creation date, last modified date and owner.

    The code that pulls the file names in for me now is linked in post #1.

  8. #8
    aquabp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    69
    Hi ssanfu,

    Thanks, for some reason I did not paste in the public function or end function line when I posted it, but that is what I have them at. I did try it as a public sub but that didn't work either. Looking at other code examples and looking up the methods to see what they are trying to do in the code so I can apply to this one and learn a bunch about vba coding.
    Last edited by June7; 01-13-2016 at 05:21 PM.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I'm getting confused on what you are trying to accomplish and where you are in this process.

    You said:
    Looking at the info in the link it looks like this method pulls info for a particular file not a group of files.
    You want to be able to select a folder and get the info for all of the files?



    I have created a table called filename with Attributes, Date Created, Date last Accessed, Date Last Modified, Drive, Path, and Size.
    I would name the table something like "tblFileData".
    Fields would be "FileName, Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive, Path, and Size.
    Notice no spaces in field names. The names in RED are reserved words and shouldn't be used as object names. (maybe add "File"as a prefix)


    The process as I see it:
    You need some way to select the folder/subfolders where the files are located.
    You need to specify the file type: png, jpg, bmp, txt, csv, ...
    The code from Allen Browne's site, "List files to a table", gathers the file names in the folder/subfolders.
    Then the function "GetFileInfo" would get the file info. You need to add a line in the "GetFileInfo" function (as June suggested) to write the file info to the table "tblFileData".

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I tested the code and it works fine. The file information is output to the VBA Immediate Window. It won't do anything else. If you want to save info to table, requires more code as described in post 5.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    aquabp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    69
    Hi ssanfu!

    I spell the fields out for you so I don't have to explain them. So they actually are FName, FAtttb, FCreate, DLA,DLM, FDrive, FPath, FSize. Allen's code actually brings in not only the file name but the path as well. As I mentioned in post 1 we have duplicate files for the same projects hiding in the project directory. Allen's code also tracks down files in sub directories to combat just this issue. The file names are pulled with the file types so I do not have to worry about that.

    I have been able to add the FSize information to the table by going to the insert query in the code and adding this line: & ", """ & FileLen(strFolder & strTemp) & """;"

    I built this only to find it in an example code on line a little later. Just when I think I can explain what I did...... i'll understand it in time!

    Currently working on inserting File Date Creation or FDate. I am using this code: & "' , #" & FDate & "#;" but now it runs but only gives me 1 file instead of the 31,000 files it usually pulls.

    In the end the database will have a form that will use a dropdown to lookup projects and what files are connected to those projects. Using file date and size along with last modified we can start looking at the files to see which files need to be deleted, combined or kept. June7 helped create a hyperlink with the file name and path information that is being pulled down all ready. It works great! Just want to get more tools to look these files.


    So from this little bit of code can you help me find my error?
    Code:
    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
     Dim FDate As Date
     
     '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, FDate, FSize) " _
                & " SELECT """ & strTemp & """" _
                & ", """ & strFolder & """" _
                & "', #" & FDate & "#" _
                & ", """ & FileLen(strFolder & strTemp) & """;"
                
     
    
    
            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
    Thanks!

  12. #12
    aquabp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    69
    Ya, about 1am last night I came to the same conclusion, so I went back to trying to add it to Allen's code. I have the File Size working now working on File Creation Date. I posted that part of his code in post 11 in response to ssandu's post. One, the line I added for fdate is not working, two is this pulling the correct information?

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    It's not working because the code is not setting the FDate variable with a date value.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    aquabp is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    69
    Ya, ok I see. I have fixed it! I now get file name, file path, file size and file creation date. Thanks! On to last modified! I don't think I need last accessed. People have been in and out of the files so do not think it would be helpful in deciding to look at keeping or deleting a file. Thanks again!

  15. #15
    pmcd is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2017
    Posts
    1
    Hi aquabp,

    I have been having the exact same problem as you have outlined above, by any chance can you publish the fixed code for the Fdate

    Have you managed to sort out the last modified and last accessed fields ?

    Thanks for your help.

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

Similar Threads

  1. Replies: 12
    Last Post: 10-21-2018, 05:49 PM
  2. Replies: 2
    Last Post: 12-04-2015, 08:30 AM
  3. Replies: 20
    Last Post: 02-02-2015, 03:02 AM
  4. Replies: 8
    Last Post: 06-29-2014, 08:01 PM
  5. Replies: 7
    Last Post: 11-20-2012, 05:08 PM

Tags for this Thread

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