技术求助：VBA自动读取指定文件夹最新CSV至A1单元格，当前仅支持TXT

Got it, let's fix those two issues you're facing: targeting CSV files instead of TXT, and getting that data into a properly formatted table starting at A1. Here's a revised approach with explanations and full working code:

Step 1: Target CSV Files Instead of TXT #

Your original code was likely filtering for .txt files—we'll adjust that to scan specifically for .csv extensions using the FileSystemObject. We'll also properly track the most recent file by checking modification dates.

Step 2: Import CSV as a Structured Table #

Instead of reading the file as plain text (which leads to unformatted data), we'll use Excel's built-in QueryTables feature. This lets us import the CSV directly to cell A1 and automatically convert it into a formatted Excel table with sorting/filtering capabilities.

Corrected Full Code #

Sub GetMostRecentCSVAndImportAsTable()
    Dim FileSys As FileSystemObject
    Dim objFile As File
    Dim myFolder As Folder
    Dim strMostRecentFile As String
    Dim dteMostRecentDate As Date
    Dim Ws As Worksheet
    Dim qt As QueryTable
    
    ' Replace this with your actual folder path
    Const myDir As String = "C:\Your\Target\Folder\Path\" 
    
    ' Initialize objects
    Set FileSys = New FileSystemObject
    Set myFolder = FileSys.GetFolder(myDir)
    Set Ws = ThisWorkbook.Worksheets("Sheet1") ' Update to your sheet name
    
    ' Clear old data and query tables to avoid conflicts
    Ws.Cells.Clear
    For Each qt In Ws.QueryTables
        qt.Delete
    Next qt
    
    ' Find the most recent CSV in the folder
    dteMostRecentDate = DateSerial(1900, 1, 1) ' Start with a super old date
    For Each objFile In myFolder.Files
        ' Only consider CSV files
        If LCase(FileSys.GetExtensionName(objFile.Name)) = "csv" Then
            If objFile.DateLastModified > dteMostRecentDate Then
                dteMostRecentDate = objFile.DateLastModified
                strMostRecentFile = objFile.Path
            End If
        End If
    Next objFile
    
    ' If we found a CSV, import it as a table
    If strMostRecentFile <> "" Then
        ' Create QueryTable to import CSV to A1
        Set qt = Ws.QueryTables.Add( _
            Connection:="TEXT;" & strMostRecentFile, _
            Destination:=Ws.Range("A1") _
        )
        
        ' Configure CSV parsing settings
        With qt
            .TextFileParseType = xlDelimited
            .TextFileCommaDelimiter = True ' Use this for comma-separated CSVs
            ' If your CSV uses tabs, replace above with: .TextFileTabDelimiter = True
            ' If semicolons: .TextFileOtherDelimiter = ";"
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .RefreshStyle = xlOverwriteCells
            .BackgroundQuery = False
            .Refresh ' Run the import
            
            ' Convert the imported range to an Excel Table
            Ws.ListObjects.Add(xlSrcRange, .ResultRange, , xlYes).Name = "LatestCSVTable"
        End With
    Else
        MsgBox "No CSV files found in the specified folder!", vbExclamation
    End If
    
    ' Clean up objects
    Set objFile = Nothing
    Set myFolder = Nothing
    Set FileSys = Nothing
    Set qt = Nothing
End Sub

Key Fixes & Explanations #

• CSV Filtering: The check LCase(FileSys.GetExtensionName(objFile.Name)) = "csv" ensures we only look at CSV files, ignoring TXT or others.
• Clean Slate: We clear the target sheet and delete old QueryTables so previous imports don't interfere with the new one.
• QueryTable Magic: This method handles CSV parsing automatically—no need to split text manually. We set the destination to A1 so the data starts exactly where you want it.
• Auto-Table Conversion: After importing, we turn the result into an Excel Table with ListObjects.Add, which gives you instant formatting, sorting, and filtering options.

Quick Setup Notes #

• Folder Path: Update C:\Your\Target\Folder\Path\ to your actual folder location.
• Sheet Name: Change "Sheet1" to the name of your worksheet.
• Delimiter: Adjust the delimiter setting if your CSV uses something other than commas (tabs, semicolons, etc.—comments in the code show how).
• Reference: Make sure you have the Microsoft Scripting Runtime enabled in the VBA editor (go to Tools > References and check it off).

This should resolve both your problems—now it'll grab the latest CSV and drop it into a properly formatted table starting at A1.

内容的提问来源于stack exchange，提问作者papacoolaid666