将数据从许多excel工作簿和工作表导入到单个工作簿/表中 [英] Importing data from many excel workbooks and sheets into a single workbook/table

问题描述

我有54​​个excel文件，每张三张纸，每张纸都有不同的数据条目，但是它们以相同的格式设置，我需要使用VBA将这些工作表中的数据导入到单个工作簿中。

有没有办法我可以编程，所以我可以构建循环来导入数据，但不必在每个工作簿名称中写入每个循环/工作表？我想我可以使用调用函数，但是我不知道如何使循环代码独立于它们所适用的工作簿名称。

非常感谢你提前，

Millie

解决方案

一个文件夹打开它们，然后环绕它们的表。根据格式的细微差异，您可能需要在导入时进行一些额外的工作。

  Sub ImportWorkbooks（目标为工作簿，importFolderPath As String）
 
 Dim objFSO As Object 
 Dim objFolder As Object 
 Dim objFile As Object 
 
设置objFSO = CreateObject（Scripting.FileSystemObject）
 
'获取与目录相关联的文件夹对象
设置objFolder = objFSO.GetFolder（importFolderPath）
 
'循环通过Files集合并导入每个工作簿
对于每个objFile在objFolder.Files 
 Dim source As Workbook 
 Set source = Application.Workbooks.Open（objFile.Path，ReadOnly：= True）
 ImportWorkbook源，目标
 wb.Close 
设置wb =没有
下一个
 
设置objFolder =没有
设置objFile =没有
设置objFSO =没有
 
 End Sub 
 
 Sub ImportWorkbook（源作为工作簿，目的地作为工作簿）
调色表作为工作表
 
'导入每个工作表
对于每个工作表在source.Sheets 
 ImportWorksheet表，目标
下一页
 End Sub 
 
 Sub ImportWorksheet（工作表作为工作表，作为工作簿的目标）
 
'在这里执行每个工作表的导入逻辑（即
 
 End Sub 
  

基本使用方式将会导入到当前工作簿中：

  ImportWorkbook ThisWorkbook c：\path\to\folder\containing\workbooks\to\import
  

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.

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

解决方案

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"

这篇关于将数据从许多excel工作簿和工作表导入到单个工作簿/表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！