Here is a link where you can get the ADO routines for copying data  from a closed
workbook:
http://www.rondebruin.nl/tips.htm

Also, here is another approach which does not use ADO:

This opens the destination wb, specifies the destination cell range and enters
formulas that reference the source wb. The source wb is not opened. The formula
results are then converted to values. Note that the "A1" in the line
r.Formula = "='" & P & "\[" & FN & "]Sheet1'!A1"
will increment "A2", "A3" ... etc for each cell in range r. So a loop isn't
necessary.

Sub TransferData()
Dim r As Range
Dim ws As Worksheet
Dim wb As Workbook
Dim P As String, FN As String
FN = Application.GetOpenFilename("Excel Files(*.xls), *.xls")
Set wb = Workbooks.Open(FN) 'Destination wb
Set ws = wb.Sheets(1) 'Destination ws
Set r = ws.Range("A1:A100") 'Destination cell range
P = wb.Path 'Assumed path of source wb same as destination wb
FN = "SourceWB.xls" 'Substitute name of source wb
r.Formula = "='" & P & "\[" & FN & "]Sheet1'!A1"
r.Value = r.Value 'Transform r formulas to values
wb.Close True 'Close destination wb
End Sub

Somebody named Greg posted the above code in reply to a similar question in
another forum.