Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cycle Thru Files in Directory starting wOldest (97)

    Perhaps this should be in the VB forum, but I'm actually doing this for an Access project. I want to import data from all the files located in a certain directory. Below is a snippet of the code I planned to use. However, ideally I need to start with the oldest file in the directory working through the most current file. (The files update the status code on accounts, and I don't want an older status code update overwriting a more current one.)

    How can I alter my code to accomodate this?

    ===================

    sFile = Dir(sPath & "*.xls")

    Do Until sFile = ""

    <Perform action on file information>

    sFile = Dir

    Loop

    =============

    Thanks so much!

    TexasTJ

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

    Re: Cycle Thru Files in Directory starting wOldest (97)

    I don't think Dir has an option to sort files by date. You can use the FileSearch object instead:

    Sub SortedLoop()
    Const sPath = "..."
    Dim varItem As Variant
    With Application.FileSearch
    .LookIn = sPath
    .FileName = "*.xls"
    .SearchSubFolders = False
    .Execute SortBy:=msoSortByLastModified
    For Each varItem In .FoundFiles
    ' perform action on varITem here
    Next varItem
    End With
    End Sub

    You must set a reference to the Microsoft Office 8.0 Object Library (in Tools | References while a module is open).

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cycle Thru Files in Directory starting wOldest (97)

    Hans,

    Thanks for the help. However, I can't seem to get the syntax to be acceptable for:

    .Execute SortBy:=msoSortByLastModified

    I'm running Access '97 on Windows 98 SE. The reference to Microsoft Office 8.0 Object Library is set.

    Meanwhile I was trying to construct an array of the filedates/filenames and sort the array, but haven't been able to get it structured properly either.

    Further suggestions?

    Thanks,

    TexasTJ

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

    Re: Cycle Thru Files in Directory starting wOldest (97)

    According to Using Microsoft Office 97 Shared Components, it should work in Office 97. I don't have a machine with Office 97 to test it on at the moment.

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

    Re: Cycle Thru Files in Directory starting wOldest (97)

    I tested on a machine with Office 97. The syntax was OK, but it seems that msoSortByLastModified is ignored. Try the code from the attached text file instead. It is not very efficient, but it should work if you have a not-too-old version of Windows/Internet Explorer (it works for me on Office 97 on Windows 95).

Posting Permissions

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