vba 从多个 Excel 工作表复制数据并使用 VBScript 将其附加到单个 Excel 工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26394905/
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
Copy data from multiple excel sheets and append that to a single excel sheet using VBScript
提问by Soumen Ray
The scenario is as follows:
场景如下：
- I have an excel (.xls) file with data. (eg. A.xls)
- The Data on this excel file are on a single worksheet (Sheet 1).
- The number of columns in this file is fixed i.e. 8
- However, the number of rows containing data may vary from time to time. (This file is updated by another program from time to time)
- Now, I have another excel file (eg. B.xls) with similar type of data but not same as the contents of A.xls.
- The number of columns in B.xlsis 8 as well. However, the number of rows containing data are unknown.
- 我有一个包含数据的 excel (.xls) 文件。（例如A.xls）
- 此 excel 文件中的数据位于单个工作表（表 1）上。
- 此文件中的列数是固定的，即 8
- 但是，包含数据的行数可能会不时变化。（此文件由其他程序不时更新）
- 现在，我有另一个 excel 文件（例如B.xls），其数据类型相似，但与A.xls的内容不同。
- B.xls 中的列数也是 8。但是，包含数据的行数未知。
I want to copy the contents of A.xls, 2nd row onwards(excluding the 1st row containing the column headers) and append/paste the same to the B.xls file, without over-writing the existing data on B.xls.
我想复制的内容A.xls，第二行起（不包括包含列标题的第一行）和附加/粘贴相同的B.xls文件，无需过度写上B.xls现有数据。
With all these details in mind, I want to write a vbscript to automate this task.
考虑到所有这些细节，我想编写一个 vbscript 来自动执行此任务。
Please help.
请帮忙。
Thanks a lot, in advance.
非常感谢，提前。
回答by Jonathan
It needs a lot of cleanup, but something like this should work. I'll clean it up a bit and then make an edit.
它需要大量清理，但这样的事情应该可以工作。我会稍微清理一下，然后进行编辑。
Sub CopyRows()
' Choose the name of the Second Workbook and last column.
' It must be in the same directory as your First Workbook.
secondWorkbook = "B.xls"
lastColumn = "H"
' A couple more variables
currentWorkbook = ThisWorkbook.Name
Workbooks.Open ThisWorkbook.Path & "\" & secondWorkbook
' In the First Workbook, find and select the first empty
' cell in column A on the first Worksheet.
Windows(currentWorkbook).Activate
With Worksheets(1).Columns("A:A")
Set c = .Find("", LookIn:=xlValues)
If Not c Is Nothing Then
' Select and copy from A2 to the end.
secondAddress = Replace(c.Address, "$A$", "")
Range("A2:" & lastColumn & CStr(CInt(secondAddress) - 1)).Select
Selection.Copy
End If
End With
' Activate the Second Workbook
Windows(secondWorkbook).Activate
With Worksheets(1).Columns("A:A")
Set c = .Find("", LookIn:=xlValues)
If Not c Is Nothing Then
' Select and paste the data from First Workbook
Range(c.Address).Select
ActiveSheet.Paste
End If
End With
End Sub
Update: That should do the trick. I copied from the wrong workbook the first time around, too. Let me know if you have questions.
更新：那应该可以解决问题。我也是第一次从错误的工作簿中复制。如果您有任何疑问，请告诉我。
回答by triggeradeadcat
This is something the Macro Recoder could have written for you. You would come out with different approach.
这是宏记录器可以为您编写的内容。你会提出不同的方法。
Turn on recording. Open A.xls and B.xls. Move down one row on a. Press Shift+Endthen →, then Shift+End+↓. Then Ctrl+Cto copy your data. Switch back to B. End+↓, ↓. Ctrl+Vto paste. Turn off recording.
开启录音。打开 A.xls 和 B.xls。在 a 上向下移动一行。按Shift+End然后→，然后按Shift+ End+ ↓。然后Ctrl+C复制您的数据。切换回 B。End+ ↓, ↓. Ctrl+V粘贴。关闭录音。
You can record in Excel.
您可以在 Excel 中记录。
Alt+T,M,R
Alt+ T, M,R
then Homekey then ↑. Stop recording.
然后Home键然后↑。停止录音。
Look what Excel wrote
看看 Excel 写了什么
Selection.End(xlUp).Select
or if you had of recorded Go To dialog
或者如果您录制了“转到”对话框
Application.Goto Reference:="R1C1"
or if you had of recorded Ctrl+Home
或者如果你有记录Ctrl+Home
Range("A1").Select
To convert to vbscript
转换为 vbscript
Record the steps in excel macro recorder. You have to rewrite it a bit because it uses a type of syntax that vbs doesn't.
在excel宏记录器中记录步骤。您必须稍微重写一下，因为它使用了一种 vbs 没有的语法。
This applies (I don't have a medium9) xlRangeAutoFormatAccounting4in vba.
这xlRangeAutoFormatAccounting4在 vba 中适用（我没有 medium9）。
Selection.AutoFormat Format:=xlRangeAutoFormatAccounting4, Number:=True, _
Font:=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
So first look up constants in vba's object browser. xlRangeAutoFormatAccounting4 = 17
所以首先在 vba 的对象浏览器中查找常量。 xlRangeAutoFormatAccounting4 = 17
Then look the function up in object browser and look at the bottom for the function definition,.
然后在对象浏览器中向上查找函数，并在底部查找函数定义。
Function AutoFormat([Format As XlRangeAutoFormat = xlRangeAutoFormatClassic1], [Number], [Font], [Alignment], [Border], [Pattern], [Width])
So the vba becomes in vbs (and vbs works in vba) (and as you can see you can work out the correct way without needing to look the function up usually)
所以vba变成了vbs（而vbs在vba中工作）（正如你所见，你可以找出正确的方法，而无需通常查找函数）
Selection.AutoFormat 17, True, True, True,True, True, True
So your code becomes
所以你的代码变成
objXLWs.Range("A3").CurrentRegion.Select.AutoFormat 17, True, True, True,True, True, True
