I'm using this code to automate a import(more like copy data) and some light formatting on a file (well a sheet in a file) that I can (have to) generate on daily bases.

What I do before even running this code is that I go to te folder/path where the new file/rapport lands in. This files name always different but at least starts with _pr11*, so it looks like this "_pr11(somenumbers).xlsx" I open this file, than Save As "PR11.xlsx" into another path.

So What I'm trying to archive is a bit of code that can either get the latest file depending on when it got into the folder or a code which can based on some other logic figure out which file is the latest rapport.

I did have another approach which was to look into the files and see which has less or mor rows (columns are always the same and never change) but this was not a smart way to do this since the rows can be less in total in one day and more the next day. So way is not the right way to do it I suppose..

    Sub ImportData()
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set closedBook = Workbooks.Open("C:\Temp\PR11.xlsx")
    closedBook.Sheets("Analyse").Copy After:=ThisWorkbook.Sheets("PR11_P3")
    closedBook.Close SaveChanges:=False
           
    Sheets("Analyse").Select
    Columns("AC:AE").Delete
    Columns("O:Q").Delete
    Columns("I:M").Delete
    Columns("E:G").Delete
    Columns("A:B").Delete
    
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("PR11_P3").Select
    Range("A10").Select
    ActiveSheet.Paste
    
    Sheets("Analyse").Delete

    Dim Table As ListObject
    Set Table = ActiveSheet.ListObjects.Add(xlSrcRange, _
    Range("A10").CurrentRegion, , xlYes)
        With Table
            .Name = "PR11_P3_Tabell"
        End With
    ActiveSheet.ListObjects("PR11_P3_Tabell").TableStyle = "TableStyleMedium5"
    ActiveSheet.ListObjects("PR11_P3_Tabell").ShowTotals = False
    
End Sub

CodePudding user response：

Open Latest File in Folder By Using FileDateTime

Sub ImportData()
    
    Const sFolderPath As String = "C:\Test\rapport\"
    
    ' Open the newest file.
    
    Dim sFileName As String: sFileName = Dir(sFolderPath & "_pr11*.xlsx")
    If Len(sFileName) = 0 Then Exit Sub ' no file found
    
    Dim cuDate As Date, sFileDate As Date, cuPath As String, sFilePath As String
    
    Do Until Len(sFileName) = 0
        cuPath = sFolderPath & sFileName
        cuDate = FileDateTime(cuPath)
        Debug.Print "Current:  " & cuDate & "  " & cuPath ' print current
        If cuDate > sFileDate Then
            sFileDate = cuDate
            sFilePath = cuPath
        End If
        sFileName = Dir
    Loop
    Debug.Print "Result:   " & sFileDate & "  " & sFilePath ' print result
    
    ' See in the VBE Immediate window 'CTRL G' what was checked
    ' and what was determined to open.
    ' When done testing, out-comment or delete the two 'Debug.Print' lines.
    
    Dim closedBook As Workbook: Set closedBook = Workbooks.Open(sFilePath)
    
    ' Continue
    
End Sub