Hi there

One way to do it would be to use the file system object. This method
is handy if you don't want to hard code all the file names you want to
check. It works by checking all the files in the given folder (C:
\Test) in this example. It will loop through all the files and check
for the excel file extension when it files the files it will open them
read the value in B6 and then close the file and write the stored
value into the next available cell in column B of the workbook that
holds the code.

Sub GetCellB6FromEachFile()

Dim intRow As Integer
Dim objFSO As Object
Dim objFile As Object
Dim objFolder As Object
Dim vntB6Value As Variant
Dim wkbk As Workbook

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.getfolder("C:\Test")
intRow = 1

For Each objFile In objFolder.Files
If Right(objFile.Name, 4) = ".xls" Then
Set wkbk = Workbooks.Open(objFile)
With wkbk
vntB6Value = Sheets(1).[B6].Value
wkbk.Saved = True
.Close
Set wkbk = Nothing
End With
ThisWorkbook.Sheets(1).Cells(intRow, 2).Value = _
vntB6Value
intRow = intRow + 1
End If
Next

Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing

End Sub

I hope this helps

Steve