Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Location
    Berkshire, UK
    Posts
    30

    Unanswered: How to import from Excel spreadsheet by file date?

    Every week I receive an Excel spreadsheet that I store in a directory then manually import to a table in my database. I know how to use the TransferSpreadsheet method in VBA to achieve this for a named spreadsheet, but what I would like to do is automatically import the most recent spreadsheet in a directory regardless of filename.

    In other words, if my directory contained say 10 Excel files, each with a different name, my database would only import the file with the most recent date.

    Is this possible please?

    Mike

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Yes, you can use the File object of the VBScript library to retrieve the date of creation of the files:
    Code:
    Sub ShowFolderList(strFolderName As String)
    
        Dim fso, Folder, File, Files
        
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set Folder = fso.GetFolder(strFolderName)
        Set Files = Folder.Files
        For Each File In Files
            Debug.Print File.Name, File.DateCreated, File.DateLastAccessed, File.DateLastModified
        Next
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Nov 2009
    Location
    Berkshire, UK
    Posts
    30
    Thank you for posting that.

    I'll have a play with it.

    Mike

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    Nov 2009
    Location
    Berkshire, UK
    Posts
    30
    OK, I see how that works but I need a bit of extra help please.

    Having got the values for File.DateCreated I need to select the most recent file in the directory then use the TransferSpreadsheet method to import it to Access.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In the loop, cache the date and name. On each pass, check if the date for the current file is more recent than the cached one. If it is then cache those details.

    Once you exit the loop you have the name of the most recent file.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2009
    Location
    Berkshire, UK
    Posts
    30
    Quote Originally Posted by pootle flump View Post
    In the loop, cache the date and name. On each pass, check if the date for the current file is more recent than the cached one. If it is then cache those details.

    Once you exit the loop you have the name of the most recent file.
    Thank you pootle flump.

    Mike

Posting Permissions

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