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

    FileDateTIme (97)

    Good Evening 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.

    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"

    Today Hans suggested the following code and I tried it.

    ' 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
    ...


    I did exactly as Hans suggested but get "Not a valid Path" error at this line of code...can anyone offer a suggestion as to what I might do at this point. I've tried many things to proceeds from here and all fails.

    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>

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: FileDateTIme (97)

    Getfolder requires the path to a folder.
    so try changing
    <pre> Set fFolder = fso.GetFolder("E:IndividualDocumergeBoli*.met") </pre>

    to
    <pre> Set fFolder = fso.GetFolder("E:IndividualDocumergeBoli")</pre>

    Regards
    John



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

    Re: FileDateTIme (97)

    JOHN,

    OMG!!!!!! Thanks so much...Perfect...I appreciate it...many thanks to Pat, Hans and John for help with this.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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