Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retrieve Modified Date? (97)

    Good Morning All,

    Yesterday Pat was most helpful with the following code to retrieve the file names from a directory and put them in a table, I was wondering if it was possible to take it one step further and retrieve the modified date also.

    Thanks.

    Dim dbs As DAO.Database, rs As DAO.Recordset
    Dim strFileName As String
    Set dbs = CurrentDb()
    Set rs = dbs.OpenRecordset("tblFilenameMets")

    DoCmd.OpenTable "tblFileNameMets", acViewNormal, acEdit
    DoCmd.RunCommand acCmdSelectAllRecords
    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.SetWarnings True

    strFileName = Dir("E:IndividualDocumergeBoli*.met")
    Do While strFileName <> ""
    rs.AddNew
    rs!FileName = strFileName
    rs.Update
    strFileName = Dir
    Loop
    Set rs = Nothing
    Set dbs = Nothing
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryFileNameMets", acViewNormal, acEdit
    DoCmd.SetWarnings True
    DoCmd.Close acTable, "tblFileNameMets"
    DoCmd.Close acForm, "frmGetFiles"
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Retrieve Modified Date? (97)

    To get the modified date, it's easiest to use the Windows Scripting Runtime library instead of the Dir function. Set a reference to Scrrun.dll in Tools/References... (this file is in the system folder - Windows/System or WinNT/System32 or something like that).

    Here is code you could use (I also replaced the cumbersome code to delete all records by a simple execute of a SQL statement):

    ' New declarations - need reference to Scrrun.dll
    Dim fso As New Scripting.FileSystemObject
    Dim fFolder As Scripting.Folder
    Dim fFile As Scripting.File

    Dim dbs As DAO.Database, rs As DAO.Recordset
    Dim strFileName As String
    Set dbs = CurrentDb()

    ' Delete all records from table
    dbs.Execute "DELETE FROM tblFileNameMets"

    Set rs = dbs.OpenRecordset("tblFilenameMets")
    ' Use File System Object to get at folder and files
    Set fFolder = fso.GetFolder("E:IndividualDocumergeBoli*.met")
    For Each fFile In fFolder.Files
    rs.AddNew
    rs!FileName = fFile.Name
    rs!ModifiedDate = fFile.DateLastModified
    rs.Update
    Next fFile

    ' Close recordset and release object memory
    rs.Close
    Set rs = Nothing
    Set dbs = Nothing
    Set fFile = Nothing
    Set fFolder = Nothing
    Set fso = Nothing
    ...

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve Modified Date? (97)

    Hans,

    Thanks for your help, I've got the reference set and all is well with that end of it, however, I get an error msg that says "path not found" returned from this line of code:

    Set fFolder = fso.GetFolder("E:IndividualDocumergeBoli*.met")

    I know this is a valid path because a) it works fine in the previous code and [img]/forums/images/smilies/cool.gif[/img] for test purposes I substituted the root of c: in it's place and still it returns the same error.....any suggestions?

    Thanks for your help.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Retrieve Modified Date? (97)

    Roberta,

    I mistakenly copied the entire string (including *.met) from Pat's code into the new version. John Hutchison already pointed out my mistake in the other thread you started on this subject. Sorry for the confusion.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •