如何将来自不同excel文件和工作表的数据导入一个主要的Excel文件中 [英] How to import data from different excel files and sheets into one main excel file

问题描述

我有不同的excel文件，它有多个工作表，我想导入一个主要的Excel文件。
这些文件位于不同的目录下。所以我想知道将所有这些文件中的特定单元格导入一个主要的excel文件是否可行，该文件应该在每次更改时更新。

I have different excel files which have multiple sheets that I would like to import into one main excel file. These files are under different directories. And so I'm wondering if it's feasible to import specific cells from all these files into one main excel file which should be updated at every change.

推荐答案

你可以像这样创建一个宏：

You can create a macro like this:

Sub ConsolidateWorkbooks()
    Dim myRange As String
    myRange = "A1:B2" 'Put the range you want to copy from each workbook here
    Dim thisWorkbook As String
    Dim thisWorksheet As String
    thisWorkbook = ActiveWorkbook.name
    thisWorksheet = ActiveSheet.name

    CopyPasteOneWorkbook ("C:\firstworkbook.xls")
    CopyPasteOneWorkbook ("C:\secondworkbook.xls")


End Sub

Sub CopyPasteOneWorkbook(name As String)
    Workbooks.Open Filename:=name
    range(myRange).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(thisWorkbook).Activate
    'Select the first empty cell in column A:
    Worksheets(thisWorksheet).range("A1").End(xlDown).Select
    Selection.Offset(1, 0).Select
    ActiveSheet.Paste
End Sub

如果您的工作簿都在一个文件中并且具有顺序名称（workbook1.xls，workbook2.xls等。），然后你可以创建一个 For 循环。否则，只需根据需要调用CopyPasteOneWorkbook即可。

If your workbooks are all in one file and have sequential names (workbook1.xls, workbook2.xls, etc.), then you can create a For loop. Otherwise, just put as many calls to CopyPasteOneWorkbook as you need.

这篇关于如何将来自不同excel文件和工作表的数据导入一个主要的Excel文件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！