VBA从外部工作簿复制数据追加到主工作簿表格的解决方案

问题场景 #

在MacOS系统中通过VBA将CSV工作簿的数据复制到xlsm格式的主工作簿时，原有代码仅在目标为普通单元格时可正常运行，若目标为表格列则会直接覆盖已有内容，无法实现追加写入。

原有问题代码 #

Sub InsertData()


Dim Proactive, mybook As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

  Set Proactive = Workbooks("Proactive Cleaning_V2.0.1.xlsm").Worksheets("ERROR207")
  Set mybook = Workbooks("ERROR207.csv").Worksheets(1)


  lCopyLastRow = mybook.Cells(wsCopy.Rows.Count, 1).End(xlUp).Row
  lDestLastRow = Proactive.Cells(Proactive.Rows.Count, 4).End(xlUp).Offset(1,0).Row

  wsCopy.Range("A2:A" & lCopyLastRow).Copy wsDest.Range("A" & lDestLastRow)
  wsCopy.Range("B2:B" & lCopyLastRow).Copy wsDest.Range("B" & lDestLastRow)


End Sub

修复后可用代码 #

修复了变量未定义、目标行定位错误的问题，同时新增了从源CSV文件名提取日期写入第三列的功能（适配文件名格式为xyz-2021-10-26的场景，不需要可自行删除对应逻辑）：

Sub InsertData()


Dim Proactive, mybook As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Dim Dte, s As String

  Set Proactive = Workbooks("Proactive Cleaning_V2.0.1.xlsm").Worksheets("ERROR207")
  Set mybook = Workbooks("ERROR207.csv").Worksheets(1)

        lCopyLastRow = mybook.Sheets(1).Cells(mybook.Sheets(1).Rows.Count, 1).End(xlUp).Row
        lDestLastRow = Proactive.Sheets("ERROR207").Cells(Proactive.Sheets("ERROR207").Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                    
        s = mybook.Name
        Dte = Left(Mid(s, InStr(s, "-") + 8), InStr(Mid(s, InStr(s, "-") + 8), ".") - 1)
        '从源CSV文件名中提取日期，写入到新增的C列
        mybook.Sheets(1).Range("C2:C" & lCopyLastRow).Value = Dte
                    
        '将CSV文件数据复制到xlsm主工作簿
        mybook.Sheets(1).Range("A2:A" & lCopyLastRow).Copy Proactive.Sheets("ERROR207").Range("A" & lDestLastRow)
        mybook.Sheets(1).Range("B2:B" & lCopyLastRow).Copy Proactive.Sheets("ERROR207").Range("B" & lDestLastRow)
        mybook.Sheets(1).Range("C2:C" & lCopyLastRow).Copy Proactive.Sheets("ERROR207").Range("C" & lDestLastRow)

        '关闭CSV文件，不保存修改
        mybook.Close savechanges:=False

End Sub

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