import data from closed files

hi,

Please, i need advice with the following, probably it's easy, but it keeps me puzzled, and i'm stuck in a loop!

1) i import a txt file with contains a column with file names.
2) then i made a vb which retrieves the data per (closed) file (always same range)
3) when vb paste the data in a sheet, it always overwrite the preceding data (A1..D5); i just can't get it done.

i think it's something with executing the macro, i can't get to paste it right, ie under the latest pasted data.

thanks in advance!!!

'my' code:

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

Worksheets(1).Activate
GetValue = ExecuteExcel4Macro(arg)
End Function

Sub TestGetValue2()
Dim c As Long, r As Long, i As Long
c = 2 'column
r = 1 'Row
p = "G:\MSO\out-survey"
While Cells(r, c).Value <> ""
f = Cells(r, c).Value
's = "file00.." 'sheet is filename minus .xls
s = Left(f, 8)
For c2 = 1 To 4
For r2 = 1 To 5
a = Cells(r2, c2).Address
Cells(r2, c2) = GetValue(p, f, s, a)
Next r2
Next c2
Worksheets(2).Activate
r = r + 1
Wend
End Sub

There doesn't appear to be any code to find the first blank row in the sheet you're pasting into. You start the loop at 1 in both cases. You should be starting R loop at a number held in a variable that contains the value of the first blank row in the destination sheet. Also, I don't see where you've declared the R2 and C2 variables. Do you have Option Explicit at the top of the module?

you're right, i didn't declare some variables and didn't use option explicit. (i do now)
finding the first empty cell is a good idea, i was completely focused on counting variables to determine cell position. i'll try to find some posts about the empty cell option. thnx!

i still have a problem with determining the last cell.
the arg(ument) in the ExecuteExcel4Macro places the retrieved value in ie. row1,col1 with as starting reference point: range("A1").
how do i make this dynamic??

please advice,

arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range(start).Address(, , xlR1C1)
'Range(ref).Range("A1").Address(, , xlR1C1)

Worksheets(1).Activate
GetValue = ExecuteExcel4Macro(arg)