You can't determine from a closed wb the number rows of data in a range
directly. All you can do is link to the wb using formulas as I have done.
However, you can assume that there will be at the very most, say 10000 rows
of data, and have the code apply formulas to a range of cells in the
destination wb that reference these 10000 cells. In other words, instead of
using A1:A100 as in my previous example, use A1:A10000.

You must also adjust the formula so that it will return blank ("") if the
referenced cell in the source wb is blank (""). Then, after converting the
formula to values, the formulas that returned blank will instead just be
blank. And after saving and reopening the destination wb the used range will
only be the range containing nonblank values instead of all the way down to
row 10000 (so the scroll bar won't be shrunken).

You could reference the entire column instead of just A1:A10000 but I would
expect a performance problem.

Example code:

Sub TransferData()
Dim r As Range
Dim ws As Worksheet
Dim wb As Workbook
Dim P As String
Dim FN As Variant

FN = Application.GetOpenFilename("Excel Files(*.xls), *.xls")
If FN = False Then Exit Sub
Set wb = Workbooks.Open(FN) 'Destination wb
Set ws = wb.Sheets(1) 'Destination ws
Set r = ws.Range("A1:A10000") 'Destination cell range
P = wb.Path 'Assumed path of source wb same as destination wb
FN = "SourceWB.xls" 'Name of source wb
r.Formula = "=If('" & P & "\[" & FN & "]Sheet1'!A1=" & _
""""", """", '" & P & "\[" & FN & "]Sheet1'!A1)"
r.Value = r.Value 'Transform r1 formulas to values
wb.Close True 'Close destination wb
End Sub

Hope it works.

Regards,
Greg