如何从500个.xls Excel文件中的单元格中获取数据? [英] How to obtain the data from cell from 500 .xls Excel files?

问题描述

我想问你如何从许多Excel .xls文件的几个确定的(且始终相同)的单元格中获取数据，即我在一个文件夹中有一个.xls文件列表，每个文件内部都有相同的表，但值不同.我想从文件夹中的所有文件中的A1，C2，E3中获取数据，并将它们放到新的Excel文件中的新表中.

I'd like to ask you how can I get the data from few determined (and always same) cells from many Excel .xls files, I.e. I have a list of .xls files in one folder, and each file has the same table inside, but with the different values. I would like to obtain the data from A1, C2, E3 from all files in folder and put them together into a new table in the new Excel file.

请问有办法吗? :) 谢谢！ ;)

Is there a way how to do it, please? :) Thanks! ;)

推荐答案

我按如下方式检索外部数据:

I retrieve external data as follows:

创建一个名为"x" 的工作表，该工作表为我要获取的每一项数据指定以下信息:

Create a worksheet called "x" that specifies the following info for each item of data I want to get:

所以我在 A，B，C，D

然后运行以下宏:

Sub GetExternalData()

    Dim wbPath As String, WorkbookName As String
    Dim WorksheetName As String, CellRef As String
    Dim Ret As String, i As Long, N As Long

    For i = 1 To Sheets("x").Cells(Rows.Count, 1).End(xlUp).Row

        wbPath = Sheets("x").Cells(i, 1).Value
        WorkbookName = Sheets("x").Cells(i, 2).Value
        WorksheetName = Sheets("x").Cells(i, 3).Value
        CellRef = Sheets("x").Cells(i, 4).Value

        Ret = "'" & wbPath & "[" & WorkbookName & "]" & _
              WorksheetName & "'!" & Range(CellRef).Address(True, True, -4150)

        Sheets("x").Cells(i, 5).Value = ExecuteExcel4Macro(Ret)
    Next i
End Sub

宏将用数据填充 E 列.

在您的情况下， A 列将被复制的值填充，因为您的文件都在一个文件夹中.

In your case column A will be filled with replicated values since your files are all in a single folder.

这篇关于如何从500个.xls Excel文件中的单元格中获取数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！