Do some search in this nice news group and youll find the answer of your
question easy.
Here is one reliable answer posted by Tom Ogilvy:

Do it with code I am sure (this is the fastest way to
do it)

say you wanted A1:A10 from sheet1 of the closed work in F1:F10 of the
activesheet


Sub GetData()
With Range("F1:F10")
.Formula = "='C:\Directory\[excelfile.xls]Sheet1'!A1"
.Formula = .Value
End With
End Sub

If you closed file is like a data base and you want to "pull the data", you
can use ADO.
http://www.erlandsendata.no/english/...php?t=envbadac

Another way is to open the file after you turn screen updating off.
Transfer the information, then close it.

Application.ScreenUpdating = False
' open the file and get the info
Application.ScreenUpdating = True

no one is the wiser unless it takes a long time to open the workbook.