Is it possible for a VBA macro to loop through files in the download folder in order to import the last (most recent) CSV ?

VBA宏是否可以遍历下载文件夹中的文件以导入上一个(最新的)CSV？

ChatGPT has suggested the following.

ChatGPT提出了以下建议。

Sub ImportLastCSVFile()    
    Dim strPath As String
    Dim strFile As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim strRawPath As String

    ' Replace with the correct download directory
    strRawPath = "C:\Users\AlexandreBrandt\Downloads\"
    strPath = strRawPath & "*.csv"
    strFile = Dir(strPath, vbNormal)

    ' Find the last CSV file
    While Len(strFile) <> 0
        If FileDateTime(strRawPath & strFile) > FileDateTime(strRawPath & strPath) Then
            strPath = strFile
        End If
        strFile = Dir() ' Get next file
    Wend

    ' Set the workbook and worksheet
    Set wb = ThisWorkbook
    On Error Resume Next
    Set ws = wb.Sheets("Import")
    If Not ws Is Nothing Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
    End If

The VBE debugger is not accepting
If FileDateTime(strRawPath & strFile) > FileDateTime(strRawPath & strPath) Then

如果FileDateTime(strRawPath&strFile)>FileDateTime(strRawPath&strPath)，则VBE调试器不接受

Is there a better way to write the loop through the files?

有没有更好的方法来编写遍历文件的循环？

Import the Latest CSV File

Sub ImportLatestCSVFile()
    
    ' Define constants.
    
    Const SRC_ROOT_FOLDER_PATH As String = "C:\Users\AlexandreBrandt\Downloads\"
    Const SRC_FILE_PATTERN As String = "*.csv"
    Const DST_SHEET_NAME As String = "Import"
    
    Dim dwb As Workbook: Set dwb = ThisWorkbook ' workbook containing this code
    
    ' Attempt to store the first file name in a variable ('CurrentFileName').
    
    Dim DirPath As String: DirPath = SRC_ROOT_FOLDER_PATH & SRC_FILE_PATTERN
    
    Dim CurrentFileName As String: CurrentFileName = Dir(DirPath)
    
    If Len(CurrentFileName) = 0 Then
        MsgBox "No files found.", vbCritical
        Exit Sub
    End If
    
    ' Store the path of the latest file in a variable ('LatestFilePath').
    
    ' Declare additional variables used in the loop.
    Dim CurrentFilePath As String, LatestFilePath As String
    Dim CurrentDateTime As Date, LatestDateTime As Date
    
    ' Loop through all the files applying the logic...
    Do While Len(CurrentFileName) > 0
        CurrentFilePath = SRC_ROOT_FOLDER_PATH & CurrentFileName
        CurrentDateTime = FileDateTime(CurrentFilePath)
        If CurrentDateTime >= LatestDateTime Then ' greater than or equal to max
            LatestDateTime = CurrentDateTime
            LatestFilePath = CurrentFilePath
        'Else ' less than max; do nothing
        End If
        CurrentFileName = Dir ' next file
    Loop

    ' Attempt to delete the destination worksheet ('dws').
    
    Dim dws As Worksheet
    
    On Error Resume Next
        Set dws = dwb.Sheets(DST_SHEET_NAME)
    On Error GoTo 0
    
    If Not dws Is Nothing Then ' the worksheet exists
        Application.DisplayAlerts = False ' delete without confirmation
            dws.Delete
        Application.DisplayAlerts = True
    'Else ' the worksheet doesn't exist; do nothing
    End If

    ' Continue, e.g.:
    
'    ' Copy the one and only worksheet in the source file ('.csv')
'    ' as the last sheet in the destination workbook.
'
'    Dim swb As Workbook: Set swb = Workbooks.Open(LatestFilePath)
'    Dim sws As Worksheet: Set sws = swb.Sheets(1)
'
'    sws.Copy After:=dwb.Sheets(dwb.Sheets.Count) ' copy as last sheet
'    swb.Close SaveChanges:=False ' close without saving changes
'
'    ' Reference and rename the destination worksheet and save
'    ' the destination workbook.
'
'    Set dws = dwb.Sheets(dwb.Sheets.Count)
'    dws.Name = DST_SHEET_NAME
'    'dwb.Save
'    
'    ' Inform.
'    
'    MsgBox "Latest file imported.", vbInformation        

End Sub

You will just need to store in a variable the latest date and in another variable the latest file. Whenever you find a newer file, just override them:

您只需将最新日期存储在一个变量中，并将最新文件存储在另一个变量中。每当您找到较新的文件时，只需覆盖它们：

    strFile = Dir(strPath, vbNormal)
    Dim strLatestFile As String = ""
    Dim currentDate As DateTime
    Dim latestDate As DateTime

    ' Find the last CSV file
    While Len(strFile) <> 0
        If strLatestFile.Equals("") Then
            strLatestFile = strFile
            currentDate =  = FileDateTime(strRawPath & strLatestFile)
            latestDate = currentDate
        End If
        If currentDate > latestDate Then
            strLatestFile = strFile
            latestDate = currentDate
        End If
        strFile = Dir() ' Get next file
        currentDate = FileDateTime(strRawPath & strFile)
    Wend

Now, you can use strLatestFile however you please.

现在，您可以随心所欲地使用strLatestFile。

It's close.. Replace the initial chunk of code (down to Wend) with:

很近..。将初始代码块(一直到Wend)替换为：

Dim strPath As String
Dim strFile As String
Dim strLatestFile As String
Dim wb As Workbook
Dim ws As Worksheet
Dim strRawPath As String

' Replace with the correct download directory
strRawPath = "C:\Users\AlexandreBrandt\Downloads\"
strPath = strRawPath & "*.csv"
strFile = Dir(strPath, vbNormal)
strLatestFile = strFile

' Find the last CSV file
While Len(strFile) <> 0
    If FileDateTime(strRawPath & strFile) > FileDateTime(strRawPath & strLatestFile) Then
        strLatestFile = strFile
        Debug.Print "Latest file set to : " & strFile
    End If
    strFile = Dir() ' Get next file
Wend
strFile = strLatestFile