Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Automatically select/open most recent file

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Schiphol Airport, Netherlands
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    We have to use data in a worksheet from an excel file that somebody else puts in a fixed directory on our network every day. Each day this source file has another name with the date included in the name. E.g. today's file would be called DRAAF20020426.xls and tomorrow's file would be called DRAAF20020427. The older files are not deleted.
    Would it be possible to use VBA to automatically select the most recent file in this source directory and open it?

    Any help or suggestion or hopefully solution would be very much appreciated.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Tested this.
    Seems to work.
    The only thing you should
    need to do is add the correct path.


    Sub FindLatestFile()
    Dim i
    Dim DateOne As Date
    Dim DateTwo As Date
    Dim YourPath As String
    Dim YourFile As String

    YourPath = "C:YourPath" 'change this to the correct path

    With Application.FileSearch
    .NewSearch
    .LookIn = YourPath
    If .Execute() > 0 Then
    For i = 1 To .FoundFiles.Count
    If IsDate(Left(Right(.FoundFiles(i), 8), 2) & "/" & _
    Left(Right(.FoundFiles(i), 6), 2) & "/" & _
    Left(Right(.FoundFiles(i), 12), 4)) Then
    DateTwo = Left(Right(.FoundFiles(i), 8), 2) & "/" & _
    Left(Right(.FoundFiles(i), 6), 2) & "/" & _
    Left(Right(.FoundFiles(i), 12), 4)
    If DateTwo > DateOne Then
    DateOne = DateTwo
    YourFile = .FoundFiles(i)
    End If
    End If
    Next i
    Else
    MsgBox "There were no files found."
    End If
    End With
    Workbooks.Open YourFile
    End Sub





    Tom


    [ This Message was edited by: TsTom on 2002-04-26 05:53 ]

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Schiphol Airport, Netherlands
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks,
    However it does not work completely correct. It does open a file, but not the most recent one. I noticed that in the code you compare datetwo with dateone. However, I find no place in the code were dateone is calculated. Could this be the problem?

    Tony

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Schiphol Airport, Netherlands
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom,
    I found the problem. It has to to with date formatting. We use MM/DD/YYYY where you probably had MM/DD/YYYY. Now it works perfectly.

    Thanks a lot for the fast solution
    Tony

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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