vba 如何将多个行从一个 Excel 表 (ListObject) 附加到另一个？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28331937/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I Append Multiple Rows from one Excel Table (ListObject) to Another?
提问by user2271875
I have two tables:
我有两个表：
Table_1 Table_2
A B C A B C
------------- -------------
1| A1| B1| C1| 1| A2| B2| C2|
2| A1| B1| C1| 2| A2| B2| C2|
3| A1| B1| C1| 3| A2| B2| C2|
Resulting table:
结果表：
Table_1
A B C
-------------
1| A1| B1| C1|
2| A1| B1| C1|
3| A1| B1| C1|
4| A2| B2| C2|
5| A2| B2| C2|
6| A2| B2| C2|
Table_2 is a temporary table (ListObject) that queries a database for entries using data connection.
Table_2 是一个临时表 (ListObject)，它使用数据连接查询数据库中的条目。
Table_1 is a table (ListObject) that acts as a collective list of entries. It is kept separate because it (1) shortens the query time in Table_2 and (2) has some programmatic edits.
Table_1 是一个表 (ListObject)，用作条目的集合列表。它是分开的，因为它 (1) 缩短了 Table_2 中的查询时间和 (2) 有一些编程编辑。
I have VBA code that copies Table_2 to Table_1 and then updates the connection string for Table_2 to not include entries with dates within the range of Table_1. The result is that Table_2 only pulls new data.
我有 VBA 代码将 Table_2 复制到 Table_1，然后更新 Table_2 的连接字符串以不包括日期在 Table_1 范围内的条目。结果是 Table_2 只拉新数据。
My code (correctly) copies data from Table_2 to Table_1:
我的代码（正确）将数据从 Table_2 复制到 Table_1：
For Each temprow in Table_2.ListRows
Set newRow = table_1.ListRows.Add
tempRow.Range.Copy
newRow.Range.PasteSpecial xlPasteValues
Next
This works great if Table_2 (new entries) only has a dozen entries. Occasionally, Table_2 will have a couple hundred entries, which will literally take 20 minutes to complete. I suspect it's because I'm doing a couple hundred iterations of .Copy and .Paste.
如果 Table_2（新条目）只有十几个条目，这很有效。有时，Table_2 会有几百个条目，这实际上需要 20 分钟才能完成。我怀疑这是因为我正在执行 .Copy 和 .Paste 的几百次迭代。
Is there a way I can do it wholesale: copy ALL of Table_2 and just make it part of Table_1? I feel like what I want to do should only take 1 second to execute programmatically, not 20 minutes. I have no conditions or exceptions. I want EVERYTHING from Table_2, which should make it easy. I'm probably going about it the wrong way. Any help is appreciated. Thanks.
有没有一种方法可以批发：复制所有 Table_2 并将其作为 Table_1 的一部分？我觉得我想做的事情以编程方式执行只需要 1 秒，而不是 20 分钟。我没有条件或例外。我想要 Table_2 中的一切，这应该很容易。我可能会以错误的方式处理它。任何帮助表示赞赏。谢谢。
回答by Ron Rosenfeld
Perhaps this, changing the worksheet appropriately:
也许这样，适当地更改工作表：
Option Explicit
Sub CombineTables()
Dim LO1 As ListObject, LO2 As ListObject
With Sheet3
Set LO1 = .ListObjects("Table_1")
Set LO2 = .ListObjects("Table_2")
End With
LO2.DataBodyRange.Copy Destination:= _
LO1.DataBodyRange.Offset(LO1.DataBodyRange.Rows.Count).Resize(1, 1)
End Sub
回答by AnalystCave.com
Try using the SQL union statement for fast table joining (ODBC):
尝试使用 SQL union 语句进行快速表连接 (ODBC)：
SELECT * FROM [Sheet1$] UNION SELECT * FROM [Sheet2$]
where Table 1 is on a sheet named "Sheet1" and Table 2 on "Sheet2".
其中表 1 位于名为“Sheet1”的工作表上，表 2 位于“Sheet2”上。
Do this by going to "Data->From other sources->From Microsoft Query"
通过转到“数据->来自其他来源->来自 Microsoft Query”来执行此操作
回答by icek
Copy the Source table and Special paste Values + formating in temp range
复制源表和特殊粘贴值 + 在温度范围内格式化
- Cut the temp range
- Add a last row to the Destination Table
- Select the Last Row
- Insert the Cut selection into the Destination Table with Shift:=xlDown
- Delete the last row it you don't want it
- 削减温度范围
- 将最后一行添加到目标表
- 选择最后一行
- 使用 Shift:=xlDown 将剪切选择插入到目标表中
- 删除你不想要的最后一行
