Greg,
That's really cool! However, I don't know the # of rows of data I need to
copy from the source file. How would you capture the last row from the source
file and be able to plug in the destination cell range to something like set
r=ws.range("A1:A"&lastrow).
Is it doable?

"Greg Wilson" wrote:

Maybe something like this. 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 = "SouceWB.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

Regards,
Greg


"matelot" wrote:

Is it true that Excel allows you to read a workbook without having it open?
If so, I want to be able to write a macro that read a closed xls data file,
open another xls and insert the data into a given worksheet and a given cell
(like starting in B2 rather than A1). Last step is to save the file and close
both files. The problem is that I don't know how big is the data file. It
could be 200K or 1MB. Is it best to use copy and paste special? Or should I
read line by line?

Thanks for the help
M