How can I extract data from multiple files in a fo

Question

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.

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)

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

Answer 1

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