Pulling Information From Multiple Excel Files

[chad13]

I have hundreds of Excel files that I need to extract data from and put in 1 sheet. The data is, fortunately, consistently located in cells K2-K6 in each of the files. I want to put all of the data in each of the individual files into 1 file.
I don't have a list of all of the names of all of the Excel files.

I'm thinking that if I can somehow get all of the names of the files into an Excel sheet, then I could put a formula in to extract the appropriate data from the various files. Any suggestions?

Thanks,

Chad

 

[chall]

This will get file names in a specifed folder:
Sub filenames()
Dim MyPath, MyDir, MyFile, MyName
Dim MyRange As Range
Application.ScreenUpdating = False
Application.StatusBar = "Updating summary results, please wait..."
MyPath = "H:\FolderName\SubFolderName\SubFolderName\"
MyFile = Dir(MyPath & "*.xls")
Do While Len(MyFile) > 0
Debug.Print MyFile
MyFile = Dir()
Loop
Application.ScreenUpdating = True
Set MyFile = Nothing
Application.StatusBar = False
MsgBox "Done!"
End Sub

This is how I get data from each file and put in a spreadsheet:
Sub GetDetail()

Dim MyPath, MyDir, MyFile, MyName
Dim r As Integer
Dim rr As Integer
Dim c As Integer
Dim n As Integer
Dim MyRange As Range
Dim MyRange2 As Range
Dim MyRange3 As Range
Application.ScreenUpdating = False
Application.StatusBar = "Updating detail data, please wait..."
MyPath = Worksheets("Setup").Range("A3")' Example H:\FolderName\SubFolderName\SubFolderName\
MyFile = Dir(MyPath & "*.xls")'gets first filename
r = 0
c = 0
Set MyRange = Worksheets("Detail").Range("c2")
Do While Len(MyFile) > 0
Workbooks.Open Filename:=MyPath & MyFile
Set MyRange2 = Workbooks(MyFile).Sheets("Sheet1").Range("B3")
Set MyRange3 = Workbooks(MyFile).Sheets("Sheet2").Range("B3")
n = 10
For rr = 0 To 10
If MyRange2.Offset(rr, 0) <> 0 Then
MyRange.Offset(r, c - 2) = Workbooks(MyFile).Sheets("Other").Range("F2")
MyRange.Offset(r, c - 1) = Workbooks(MyFile).Sheets("Other").Range("B9")
MyRange.Offset(r, c) = MyRange2.Offset(rr, -1) 'Stand
MyRange.Offset(r, c + 1) = MyRange3.Offset(rr, 1) 'ID
MyRange.Offset(r, c + 2) = MyRange2.Offset(rr, 0) 'Type
MyRange.Offset(r, c + 3) = MyRange2.Offset(rr, 1) 'Weight
MyRange.Offset(r, c + 4) = MyRange2.Offset(rr, 2) 'Age
MyRange.Offset(r, c + 5) = MyRange2.Offset(rr, 3) 'Value1
MyRange.Offset(r, c + 6) = MyRange2.Offset(rr, 6) 'Value2
MyRange.Offset(r, c + 7) = MyRange2.Offset(rr, 6) - MyRange2.Offset(rr, 3) 'Difference
r = r + 1
End If
Next rr
Workbooks(MyFile).Close (False)
MyFile = Dir()'gets next file
Loop
Application.ScreenUpdating = False
Application.StatusBar = False
Set MyFile = Nothing
MsgBox "Done!"

End Sub