如何从Excel文件夹中的多个文件中提取数据 [英] How can I extract data from multiple files in a folder of excel

问题描述

我在一个文件夹中有140个excel文件，这些文件具有相同的格式，并且在每个文件中都有一个名为数据"的工作表名称(隐藏)，其中我的信息范围为c2:c12.我需要一个宏来从文件夹中的这140个excel文件中提取所有数据，并应逐行粘贴到摘要表中.(从C2toC12中提取的信息，必须转置)每个文件信息都应逐行粘贴.

I have 140 excel files in a folder that have the same identical format, and in each file have a sheet name called "data" (which is hidden) where I have information range c2:c12. I need a macro to extract all the data from these 140 excel files in a folder and should be pasted in my summary sheet in row wise. (the information extracted from C2toC12, has to transpose) each file information should be pasted row wise.

示例:C2 =文件名，C3 =名称，C4 =父亲姓名，C5 =年龄等.直到C12

Example: C2 = File Name, C3=Name,C4=Father Name,C5=Age and etc.. upto C12

所有提取的信息都应使用转置(行方式)粘贴到我的摘要表中

All the extracted information should be pasted into my summary sheet with transpose (row wise)

注意:提取数据时，我已经有了提取数据的路径，该路径在我的摘要表"E1"中

Note: while extracting the data, I have already the path from where it has to extract and which is in my summary sheet "E1"

在这里向我指出正确方向的任何帮助将不胜感激

Any help in pointing me in the right direction here would be most appreciated

推荐答案

您可以使用FileSystemObject获取所需文件的所有名称.然后想让你循环

You can use the FileSystemObject to get all the name of the files you need. and then do want you want in a loop

Dim fso As Object
Dim folder As Object
Dim file As Object
Dim xlWb As Workbook

Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder("your\folder")

for each file in folder.files
  Set xlWb = Workbooks.Open(file.Path & "\" & file.Name)
  'your code here
  xlWb.Close
next

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