''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 executeexcel4macro 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 = Getfromclosedfile(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 Getfromclosedfile(ByVal wbpath As String, wbname As String, wsname As String, cellref As String) As Variant
Dim arg As String
getinfofromclosedfile = ""
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
Getfromclosedfile = ExecuteExcel4Macro(arg)
End Function