vba 将许多 Excel 工作簿和工作表中的数据导入单个工作簿/表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4610498/
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
Importing data from many excel workbooks and sheets into a single workbook/table
提问by Max Rusalen
I have 54 excel files with three sheets each, each sheet has a different amount of data entries but they are set out in a identical format, and I need to import the data from those sheets into a single workbook using VBA.
我有 54 个 excel 文件，每个文件有三张工作表，每张工作表都有不同数量的数据条目，但它们以相同的格式列出，我需要使用 VBA 将这些工作表中的数据导入到一个工作簿中。
Is there any way I can program it so I can build the loops to import the data, but without having to write in each workbook name for each loop/sheet? I think I can use the call function, but I don't know how to make the loop codes independent of the workbook name they apply to.
有什么方法可以对它进行编程，以便我可以构建循环以导入数据，但不必为每个循环/工作表写入每个工作簿名称？我想我可以使用调用函数，但我不知道如何使循环代码独立于它们适用的工作簿名称。
Thank you so much in advance,
非常感谢你提前，
Millie
米莉
回答by Shaun
Sure just loop over the workbooks in a folder open them and then loop over their sheets. Depending on slight differences in format you might need to do some extra work when importing.
当然只是循环文件夹中的工作簿打开它们然后循环它们的工作表。根据格式的细微差异，您可能需要在导入时做一些额外的工作。
Sub ImportWorkbooks(destination as workbook, importFolderPath As String)
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object associated with the directory
Set objFolder = objFSO.GetFolder(importFolderPath)
'Loop through the Files collection and import each workbook
For Each objFile In objFolder.Files
Dim source As Workbook
Set source = Application.Workbooks.Open(objFile.Path, ReadOnly:=True)
ImportWorkbook source, destination
wb.Close
Set wb = Nothing
Next
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing
End Sub
Sub ImportWorkbook(source As Workbook, destination as Workbook)
Dim sheet As Worksheet
'Import each worksheet
For Each sheet In source.Sheets
ImportWorksheet sheet, destination
Next sheet
End Sub
Sub ImportWorksheet(sheet As Worksheet, destination as Workbook)
'Perform your import logic for each sheet here (i.e. Copy from sheet and paste into a
'sheet into the provided workbook)
End Sub
Basic usage would be something like the following to import into the current workbook:
要导入到当前工作簿中，基本用法类似于以下内容：
ImportWorkbooks ThisWorkbook, "c:\path\to\folder\containing\workbooks\to\import"
回答by Marc Thibault
It only takes two things: An array with the workbook file names in it, e.g.
它只需要两件事：一个包含工作簿文件名的数组，例如
dim books
books = array("book1.xls","book2.xls",....)
Then your loop code looks something like
然后你的循环代码看起来像
dim myBk as Workbook
dim bkFile as string
For Each bkFile in books
myBk = Workbooks.Open(bkFile, ReadOnly)
myBk.Activate
'Transfer cells from myBk to target workbook
target.cells(--).Value = myBk.Sheets("myStuff").Cells(--)
...
Next
I can't help you with the detail. You'll need to change the target.cells argument for each pass through the loop to shift the data destination.
细节我帮不了你。您需要为每次循环更改 target.cells 参数以移动数据目标。
