Question

如何把多份EXCEL文件里的所有工作簿中的指定某一列经过批处理提取汇总到一份新表里？最好用简单的vbs实现。

如何把多份EXCEL文件里的所有工作簿中的指定某一列经过批处理提取汇总到一份新表里？例如我有一个文件夹，里面有很多EXCEL文件，我想把全部文件里的B列汇总到一份新的EXCEL文件里。要怎样实现，最好用简单的vbs实现。

Answer 1

可以用VBA解决，需汇总的工作簿中文件放在D:\test下，要汇总的数据在工作表sheet1的B列第二行起，以下为程序：
Private Sub CommandButton1_Click()
Dim FolderName As String, wbName As String, r As Long, cValue As Variant
Dim wbList() As String, wbCount As Integer, i As Integer
FolderName = "d:\test"
wbCount = 0
wbName = Dir(FolderName & "\" & "*.xls")
While wbName <> ""
wbCount = wbCount + 1
ReDim Preserve wbList(1 To wbCount)
wbList(wbCount) = wbName
wbName = Dir
Wend
If wbCount = 0 Then Exit Sub
j = 2
For i = 1 To wbCount
Dim wb As Workbook
Application.ScreenUpdating = False
Set wb = Workbooks.Open(FolderName & "\" & wbList(i), True, True)
k = 2
Do Until wb.Worksheets("sheet1").Range("B" & k).Formula = ""
With ThisWorkbook.Worksheets("sheet1")
.Range("B" & j).Formula = wb.Worksheets("sheet1").Range("B" & k).Formula
End With
j = j + 1
k = k + 1
Loop
wb.Close False
Set wb = Nothing
Application.ScreenUpdating = True
Next i
End Sub

Answer 2

自己下功夫录制一次宏就可以了。再需要时执行宏即可。

Answer 3

修改汇总单元格的代码是哪里，我看不懂这个，比如说是要汇总A1：B2的内容，如何修改这个VBS