如何将多个CSV文件导入单个Excel并添加文件名至新列

原代码存在几个关键问题，导致无法实现需求：

• 未定义FolderName变量，应使用已声明的FolderPath
• 数据复制语句逻辑错误，无法将CSV内容正确粘贴到结果工作簿
• 缺少为每行添加对应CSV文件名的逻辑

以下是修正后的完整代码，同时优化了表头处理（避免重复表头）：

Sub Combinecsvs()
    Dim FolderPath As String
    Dim FileName As String
    Dim WbResult As Workbook
    Dim wsResult As Worksheet
    Dim wbCSV As Workbook
    Dim lastRowResult As Long
    Dim lastRowCSV As Long
    Dim colCountCSV As Integer
    
    ' 替换为你的CSV文件夹路径
    FolderPath = "C:\YourCSVFolder\"
    ' 确保路径末尾包含斜杠
    If Right(FolderPath, 1) <> "\" Then
        FolderPath = FolderPath & "\"
    End If
    
    Set WbResult = ActiveWorkbook
    Set wsResult = WbResult.ActiveSheet
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    FileName = Dir(FolderPath & "*.csv")
    
    Do While FileName <> vbNullString
        Set wbCSV = Workbooks.Open(FolderPath & FileName)
        With wbCSV.ActiveSheet
            lastRowCSV = .UsedRange.Rows.Count
            colCountCSV = .UsedRange.Columns.Count
            
            ' 获取结果表的最后一行位置
            lastRowResult = wsResult.Cells(Rows.Count, 1).End(xlUp).Row
            
            ' 处理表头：结果表为空时复制表头，否则跳过表头
            If lastRowResult = 1 And wsResult.Cells(1, 1) = "" Then
                .UsedRange.Copy wsResult.Cells(1, 1)
                lastRowResult = lastRowCSV
            Else
                .UsedRange.Offset(1).Copy wsResult.Cells(lastRowResult + 1, 1)
                lastRowResult = lastRowResult + (lastRowCSV - 1)
            End If
            
            ' 在新增列中填充当前CSV文件名
            wsResult.Range(wsResult.Cells(lastRowResult - lastRowCSV + 2, colCountCSV + 1), _
                          wsResult.Cells(lastRowResult, colCountCSV + 1)).Value = FileName
        End With
        
        wbCSV.Close False
        FileName = Dir()
    Loop
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

关键说明 #

• 路径设置：将FolderPath的值替换为你的CSV文件所在的实际文件夹路径
• 表头处理：如果结果工作表是空的，会保留第一个CSV的表头；后续CSV仅复制数据行，避免重复表头
• 文件名列：每个CSV的数据行都会在最右侧新增一列，填充对应的CSV文件名
• 性能优化：关闭屏幕更新和提示，提升运行速度；操作完成后恢复默认设置

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