open last modified excel spreadsheet using vba

Question

I have the following code that i have used to open the last modified CSV file, and have literally just changed the path name and extension, but it now doesnt work, would appreciate any pointers on where i am going wrong:

Code i am using:

Sub ReceiptTest()

    On Error Resume Next
    With Application.FileSearch
    .LookIn = "\\K123456\shared\IT Public\ReceiptsETE\Archive\": .Filename = "*.XLS*"
    .Execute msoSortByLastModified, msoSortOrderDescending
    For FF = 1 To .FoundFiles.Count
    If FileDateTime(.FoundFiles(FF)) > LastModDate Then
    LastModDate = FileDateTime(.FoundFiles(FF))
    lmf = .FoundFiles(FF)
    End If
    Next
    End With
    Workbooks.Open (lmf)

    End Sub

Thanks

are you actually run this in xl03? `Application.FileSearch` doesn't work in xl07 onwards — brettdj, May 31 '13 at 10:26

score 3 · Answer 1 · answered May 31 '13 at 12:57

If you're trying to open a CSV, then your filename should be .csv, not xls. Here's how I do it. You need to set a reference to Microsoft Scripting Runtime. It will work even when you upgrade from 2003

Sub OpenCSV()

    Dim sFldr As String
    Dim fso As Scripting.FileSystemObject
    Dim fsoFile As Scripting.File
    Dim fsoFldr As Scripting.Folder
    Dim dtNew As Date, sNew As String

    Const sCSVTYPE As String = "Microsoft Office Excel Comma Separated Values File"

    Set fso = New Scripting.FileSystemObject

    sFldr = "C:\Documents and Settings\dick\My Documents\QBExport\"

    Set fsoFldr = fso.GetFolder(sFldr)

    For Each fsoFile In fsoFldr.Files
        If fsoFile.DateLastModified > dtNew And fsoFile.Type = sCSVTYPE Then
            sNew = fsoFile.Path
            dtNew = fsoFile.DateLastModified
        End If
    Next fsoFile

    Workbooks.Open sNew

End Sub

Thanks, but i actually want to open an .xls file not .csv – EDDIE RODGERS May 31 '13 at 13:32 — EDDIE RODGERS, May 31 '13 at 13:32

score 0 · Answer 2 · edited Dec 08 '13 at 05:47

I cannot test your code as I am using Execl 2010 and Application.FileSearch isn't supported.

I use this to find the latest modified file...

Sub GetLatestFile()

Dim strFolder  As String
Dim strFile    As String
Dim latestFile As String
Dim dtLast     As Date

'   assign variables
    strFolder = "C:\" 'The end of this path must have a \ on it
    strFile = Dir(strFolder & "\*.*", vbNormal)    ' Any File
'   strFile = Dir(strFolder & "\*.xls*", vbNormal) ' Excel Files
'   strFile = Dir(strFolder & "\*.csv", vbNormal)  ' CSV Files

'   loop through files to find latest modified date
    Do While strFile <> ""
        If FileDateTime(strFolder & strFile) > dtLast Then
            dtLast = FileDateTime(strFolder & strFile)
            latestFile = strFolder & strFile
        End If
        strFile = Dir
    Loop

    MsgBox latestFile

End Sub

thanks for this, i have amended the strFolder to reflect the path i need, and when i run the code, but the message box that pops up is blank....any thoughts? Also, how would you open the last modified file — EDDIE RODGERS, May 31 '13 at 13:12

score 0 · Answer 3 · answered Dec 08 '13 at 05:42

Ok, I got this to work using Conman's code above with a few modification (I made changes to his code and they will be reflected if they get approved). Here is his code with those changes:

Sub GetLatestFile()

Dim strFolder  As String
Dim strFile    As String
Dim latestFile As String
Dim dtLast     As Date

'   assign variables
    strFolder = "C:\your\file\path\goes\here\" 'the path of the file drop folder (you need the final "\" on the directory
    strFile = Dir(strFolder & "\*.xls*", vbNormal) ' Excel Files
'   strFile = Dir(strFolder & "\*.csv", vbNormal)  ' CSV Files
'   strFile = Dir(strFolder & "\*.*", vbNormal)    ' Any File

'   loop through files to find latest modified date
    Do While strFile <> ""
        If FileDateTime(strFolder & strFile) > dtLast Then
            dtLast = FileDateTime(strFolder & strFile)
            latestFile = strFolder & strFile
        End If
        strFile = Dir
    Loop

    MsgBox latestFile

End Sub

You can also set strFolder by using a file dialogue and passing it into the above sub. Here is an example:

Sub ChooseFolder()
    Dim fd As Office.FileDialog
    Dim strFolder As String

    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd
        If .Show Then
            strFolder = .SelectedItems(1)
        End If
    End With

    GetLatestFile strFolder

End Sub

Sub GetLatestFile(strFolder  As String)

Dim strFile    As String
Dim latestFile As String
Dim dtLast     As Date

'   assign variables
    strFolder = strFolder & "\"
    strFile = Dir(strFolder & "\*.xls*", vbNormal) ' Excel Files
'   strFile = Dir(strFolder & "\*.csv", vbNormal)  ' CSV Files
'   strFile = Dir(strFolder & "\*.*", vbNormal)    ' Any File

'   loop through files to find latest modified date
    Do While strFile <> ""
        If FileDateTime(strFolder & strFile) > dtLast Then
            dtLast = FileDateTime(strFolder & strFile)
            latestFile = strFolder & strFile
        End If
        strFile = Dir
    Loop

    MsgBox latestFile

End Sub

I just tested both chucks of code and they work for me. Let me know if you can't get them to work.

open last modified excel spreadsheet using vba

3 Answers3

Linked