How can I retrieve multiple cell values by excelexecute4macro()

[Saptarshi Das]

''I wanted to retrieve values from multiple cells,that is return a column of data(like B7:B13) from a closed workbook and assign it as a cells value in another sheet.I have done it like the following but it is giving error at the executeexcel4ma cro step.Could somebody tell the reason for this error and any alternative solution.

Thanks in advance

Saptarshi


Sub read()
Dim foldername As String, wb As String, r As Long, cvalue As Variant
Dim wblist() As String, wbcount As Integer, i As Integer, col
Dim startingsource, endingsource, j
foldername = "D:\files"
wbcount = 0
wbname = Dir(foldername & "\" & "*.xls")
While wbname <> ""
wbcount = wbcount + 1
ReDim Preserve wblist(1 To wbcount)
wblist(wbcount) = wbname
wbname = Dir
Wend
If wbcount = 0 Then Exit Sub
startingsource = 7
endingsource = 13
r = 2
d = 7
col = 0
For i = 1 To wbcount
For j = startingsource To endingsource
cvalue = Getfromclosedfi le(foldername, wblist(i), "summary", "B7:B13")
Range("A1:A7"). Formula = cvalue
Next j
startingsource = 7
endingsource = 13
r = r + 1
col = 0

Next i
End Sub

Private Function Getfromclosedfi le(ByVal wbpath As String, wbname As String, wsname As String, cellref As String) As Variant
Dim arg As String
getinfofromclos edfile = ""
If Right(wbpath, 1) <> "\" Then wbpath = wbpath & "\"
If Dir(wbpath & "\" & wbname) = "" Then Exit Function
arg = "'" & wbpath & "[" & wbname & "]" & wsname & "'!" & Range(cellref). Address(True, True, xlR1C1)
On Error Resume Next
Getfromclosedfi le = ExecuteExcel4Ma cro(arg)
End Function