vba 将不同文件夹中的多个工作簿合并为一个
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15186302/
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
Merge Multiple Workbooks From Different Folders Into One
提问by IRHM
I wonder whether someone may be able to help me please.
我想知道是否有人可以帮助我。
I'm using the code below to allow a user to merge multiple workbooks from different folders into one 'Summary' worksheet.
我使用下面的代码允许用户将来自不同文件夹的多个工作簿合并到一个“摘要”工作表中。
Sub Merge()
Dim DestWB As Workbook, WB As Workbook, WS As Worksheet, SourceSheet As String
Set DestWB = ActiveWorkbook
SourceSheet = "Input"
startrow = 7
FileNames = Application.GetOpenFilename( _
filefilter:="Excel Files (*.xls*),*.xls*", _
Title:="Select the workbooks to merge.", MultiSelect:=True)
If IsArray(FileNames) = False Then
If FileNames = False Then
Exit Sub
End If
End If
For n = LBound(FileNames) To UBound(FileNames)
Set WB = Workbooks.Open(Filename:=FileNames(n), ReadOnly:=True)
For Each WS In WB.Worksheets
If WS.Name = SourceSheet Then
With WS
If .UsedRange.Cells.Count > 1 Then
dr = DestWB.Worksheets("Input").Range("C" & DestWB.Worksheets("Input").Rows.Count).End(xlUp).Row + 1
Lastrow = .Range("C" & Rows.Count).End(xlUp).Row
If Lastrow >= startrow Then
.Range("A" & startrow & ":AE" & Lastrow).Copy
DestWB.Worksheets("Input").Cells(dr, "A").PasteSpecial xlValues
End If
End If
End With
Exit For
End If
Next WS
WB.Close savechanges:=False
Next n
End Sub
The code works, but I'd like to tweak this a little, so that instead of the user having to manually select the files to merge, the macro reads a list of the file names and file paths and automatically copies and pastes the relevant data into the "Summary" sheet.
代码有效，但我想稍微调整一下，这样用户就不必手动选择要合并的文件，宏读取文件名和文件路径的列表并自动复制和粘贴相关数据进入“摘要”表。
I've set my Summary workbook up so there is a sheet called "Lists" with the file names listed in B3:B10 and in C3:C10 the associated file paths. I've searched "Stackoverflow" and carried out Google searches, and although I've found a number of posts on this topic, they don't show me how to read from a list of filenames and paths, but rather hard coding a specific directory which doesn't suit my needs.
我已经设置了我的摘要工作簿，所以有一个名为“列表”的工作表，其中列出了 B3:B10 和 C3:C10 中的相关文件路径的文件名。我搜索了“Stackoverflow”并进行了 Google 搜索，虽然我找到了许多关于这个主题的帖子，但它们没有告诉我如何从文件名和路径列表中读取，而是硬编码一个特定的目录不适合我的需要。
I just wondered whether someone could possibly look at this please and offer some guidance on how I may be able to acheive this.
我只是想知道是否有人可以看看这个，并就我如何实现这一目标提供一些指导。
Many thanks and kind regards
非常感谢和亲切的问候
回答by
Kindly use the addin RDBMerge.
请使用插件 RDBMerge。
RDBMerge is a user friendly way to Merge Data from Multiple Excel Workbooks, csv and xml files into a Summary Workbook
RDBMerge 是一种用户友好的方式，可以将多个 Excel 工作簿、csv 和 xml 文件中的数据合并到汇总工作簿中
