This doesn't require you to open the workbooks. Since you have 400, I would
think the workbook open/copy/close approach will be rather klunky. I just
wrote this for you just now and havn't had occasion to use it so can't
confirm its reliability. Suggest you give it a shot.

Regards,
Greg

Sub GetValsByFormula()
Dim FNs As Variant
Dim FN As String, Pth As String
Dim i As Integer

On Error GoTo ExitProc
FNs = Application.GetOpenFilename _
("Excel Files(*.xl?), *.xl?", MultiSelect:=True)
If TypeName(FNs) = "Boolean" Then Exit Sub
FN = Dir(FNs(LBound(FNs)))
Pth = Left(FNs(LBound(FNs)), Len(FNs(LBound(FNs))) - Len(FN))
For i = LBound(FNs) To UBound(FNs)
FN = Dir(FNs(i))
Cells(i, 1).Formula = "= '" & Pth & "[" & FN & "]Sheet1'!A1"
Cells(i, 2).Formula = "= '" & Pth & "[" & FN & "]Sheet1'!B1"
Next
ExitProc:
End Sub

"igorek" wrote:

Hello,

I have 400 files in the direcrory of the identical format. in order to
create a proper check tool i need my code to go into every single file pick
up values in cells A1 and B1 and populate these values in the ActiveWorkbook
in the following manner
A1 B1
File 1 x y
File 2 y z
File 3 w m

Thank you
Igor