vba - 如何将数据从csv文件复制到其他excel文件

Question

由于我是 VBA 新手，我创建了一个代码，它可以打开一个 .csv 文件并将数据从 .csv 复制到一个 excel 文件，而无需同时打开两者。

实际上它适用于 excel 文件，但是当我使用 .csv 文件时，它会显示一条错误消息“订阅超出范围”。我该如何解决这个问题？谢谢你!

    Sub Copywb1()
    Dim wkb1 As Workbook
    Dim sht1 As Worksheet
    Dim wkb2 As Workbook
    Dim sht2 As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

Setwkb1 = ThisWorkbook
Setwkb2 = Workbooks.Open("C:\Desktop\AAA.xlsx")
Setwkb1 = Workbooks.Open("C\Reports\BBB.csv")
Setsht1 = wkb1.Sheets("Reports")
Setsht2 = wkb2.Sheets("Fees")

sht1.Range("A1:BM9").Copy
sht2.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
wkb2.Close True
wkb1.Close True

  End Sub

Answer 1

这是我的一个工作示例，您应该能够相当容易地适应您的需求:

Sub demo_loadDataFromCSV()
    Const csvFile = "x:\mypath\myfile.csv"
    Dim ws As Worksheet, csv As Workbook, cCount As Long, cName As String

    ' Application.ScreenUpdating = False       'keep these commented-out until...
    ' Application.DisplayAlerts = False        ' ...done testing/troubleshooting!

    Set ws = ThisWorkbook.ActiveSheet          'remember where we parked
    Workbooks.Open csvFile                     'open the csv
    Set csv = ActiveWorkbook                   'create object of csv workbook
    cName = csv.Name                           'get name of csv while its open
    ActiveSheet.Columns("A:B").Copy            'copy columns A and B
    ws.Activate                                'go back to the first sheet
    ws.Range("A1").PasteSpecial xlPasteValues  'paste values
    cCount = Selection.Cells.Count             'count pasted cells
    csv.Close                                  'close CSV

    Application.DisplayAlerts = True           're-enable alerts
    Application.ScreenUpdating = True          'resume screen updates

    MsgBox cCount & " cells were copied from " & cName _
                  & " to " & ws.Parent.Name, vbInformation, "Done"
End Sub

更多信息:

微软文档: Workbooks.Open Method (Excel)

微软文档: Range.PasteSpecial Method (Excel) (Excel)

首席财务官:Referring to Other Worksheets or Workbooks in Excel VBA

代码 VBA:Set Workbook variable