如何获取任何 excel 文件并通过标题名称将多列复制到新工作簿(100,000+ 数据点)中

Question

我正在尝试用 VBA 编写代码，允许我获取任何 excel 文件(因为我们每周都会收到新文件)并按名称复制特定列(因为可以添加或删除列并且列名称不会更改)到新工作簿中。因此，几乎将大型数据集重新格式化为具有我需要的信息的更简化的版本。

我在网上尝试了不同的代码，但它无法运行。VBA的初学者只熟悉工作表中的较小任务。

Dim nmary As Variant
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim i As Long
Dim rng As Range
Set sh1 = ThisWorkbook.ActiveSheet
nmary = Array("FUND", "ORDERED_AMT", "CANCELLED_AMT", "NET_ORDERED", _
"DELIVERED_AMT", "BILLED_AMT", "UNINVOICED_RECEIPT_AMT", _
"EXPENDED_AMT", "UDO", "WBS TYPE", "WBS", "SOURCE", "CIP")
Workbooks.Add
Set sh2 = ActiveWorkbook.Sheets(1)
For i = LBound(nmary) To UBound(nmary)
Set rng = sh1.Rows(1).Find(nmary(i), xlValues).EntireColumn
rng.Copy sh2.Cells(1, i + 1)
Next
' Error after for loop

Answer 1

所以，你的问题是，当找不到其中一个数组值时，它会抛出并出错，你为你找到参数输入了 xlValues，但从未将其指定为 Lookin，所以你的发现试图评估 for after 作为 xlvalues 的查找。 在这里，它检查错误并告诉 find 函数查找单元格的值。 此外，您可以在创建 WB 时将 sh2 设置为新工作簿的第一页。

Sub test()
Dim nmary As Variant
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim i As Long
Dim rng As Range
Set sh1 = ThisWorkbook.ActiveSheet
nmary = Array("FUND", "ORDERED_AMT", "CANCELLED_AMT", "NET_ORDERED", _
"DELIVERED_AMT", "BILLED_AMT", "UNINVOICED_RECEIPT_AMT", _
"EXPENDED_AMT", "UDO", "WBS TYPE", "WBS", "SOURCE", "CIP")
Set sh2 = Workbooks.Add.Sheets(1)

For i = LBound(nmary) To UBound(nmary)
If sh1.Rows(1).Find(What:=nmary(i), LookIn:=xlValues) Is Nothing Then
Else
Set rng = sh1.Rows(1).Find(What:=nmary(i), LookIn:=xlValues).EntireColumn
rng.Copy sh2.Cells(1, i + 1)
End If
Next
End Sub