Macro to extract data from multiple files?

Posted by Rob on October 03, 2001 2:55 PM

Hi again, I was wondering if there is a macro i could use to open multiple excel files (all formatted the same), and extract cell J8 from each of them and put that value into a new sheet. The problem is, i have about 100 files in this directory, and all have random names. I want a list of cell J8 from each of them. Is there a way to do this? any help is appreciated, thanks

Posted by Robb on October 04, 2001 4:30 AM

Rob

Try this code - amended to suit of course. It will loop through the files in the
chosen directory, opening each and listing value of J8 wherever you
choose.

Sub Dtry()
Dim myFile As String, myCurrFile As String
myCurrFile = ThisWorkbook.Name
myFile = Dir("E:\Whatever Path\*.xls")
Do Until myFile = ""
Workbooks.Open "E:\Whatever Path\" & myFile
Workbooks(myCurrFile).Worksheets("Sheet2").Range("A1").End(xlDown).Offset(1, 0) = Workbooks(myFile).Worksheets("Sheet1").Range("J8")
Workbooks(myFile).Close savechanges:=False
myFile = Dir
Loop

End Sub

Any help?

Regards