Sub Example()

Dim wbPath As String, wbName As String
Dim wsName As String, cellRef As String
Dim Ret As String

wbPath = "http://*****/2014/"

wbName = "overview 2014.xlsm"
wsName = "Sheet1"
cellRef = "E2"

Ret = "'" & wbPath & "[" & wbName & "]" & _
      wsName & "'!" & Range(cellRef).Address(True, True, -4150)

ActiveWorkbook.Worksheets("Sheet1").Range("A4").Value = ExecuteExcel4Macro(Ret)

End Sub

wbPath = "http://*****/2014/"

 wbName = "overview 2014.xlsm"
 wsName = "Sheet1"
 cellRef = "E2"

 Ret = "'" & wbPath & "[" & wbName & "]" & _
      wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)

ActiveWorkbook.Worksheets("sheet1").Range("A4").Value = ExecuteExcel4Macro(Ret)

wbName = "workbook2.xlsm"
wsName = "sheet1"
cellRef = "E2"

Ret = "'" & wbPath & "[" & wbName & "]" & _
      wsName & "'!" & Range(cellRef).Address(True, True, -4150)

ActiveWorkbook.Worksheets("sheet1").Range("A5").Value = ExecuteExcel4Macro(Ret)

wbName = "workbook3.xlsm"
wsName = "sheet1"
cellRef = "E2"

Ret = "'" & wbPath & "[" & wbName & "]" & _
      wsName & "'!" & Range(cellRef).Address(True, True, -4150)

ActiveWorkbook.Worksheets("sheet1").Range("A6").Value = ExecuteExcel4Macro(Ret)

wbName = "workbook4.xlsm"
wsName = "sheet1"
cellRef = "E2"

Ret = "'" & wbPath & "[" & wbName & "]" & _
      wsName & "'!" & Range(cellRef).Address(True, True, -4150)

ActiveWorkbook.Worksheets("sheet1").Range("A7").Value = ExecuteExcel4Macro(Ret)

wbName = "workbook5.xlsm"
wsName = "sheet1"
cellRef = "E2"

Ret = "'" & wbPath & "[" & wbName & "]" & _
      wsName & "'!" & Range(cellRef).Address(True, True, -4150)

ActiveWorkbook.Worksheets("sheet1").Range("A8").Value = ExecuteExcel4Macro(Ret)

wbName = "workbook6.xlsm"
wsName = "sheet1"
cellRef = "E2"

Ret = "'" & wbPath & "[" & wbName & "]" & _
      wsName & "'!" & Range(cellRef).Address(True, True, -4150)

ActiveWorkbook.Worksheets("sheet1").Range("A9").Value = ExecuteExcel4Macro(Ret)

wbName = "Workbook7.xlsm"
wsName = "sheet1"
cellRef = "E2"

Ret = "'" & wbPath & "[" & wbName & "]" & _
      wsName & "'!" & Range(cellRef).Address(True, True, -4150)

ActiveWorkbook.Worksheets("sheet1").Range("A10").Value = ExecuteExcel4Macro(Ret)

wbName = "workbook8.xlsm"
wsName = "sheet1"
cellRef = "E2"

Ret = "'" & wbPath & "[" & wbName & "]" & _
      wsName & "'!" & Range(cellRef).Address(True, True, -4150)

ActiveWorkbook.Worksheets("sheet1").Range("A11").Value = ExecuteExcel4Macro(Ret)

wbName = "workbook9.xlsm"
wsName = "sheet1"
cellRef = "E2"

Ret = "'" & wbPath & "[" & wbName & "]" & _
      wsName & "'!" & Range(cellRef).Address(True, True, -4150)

ActiveWorkbook.Worksheets("sheet1").Range("A13").Value = ExecuteExcel4Macro(Ret)

wbName = "workbook10.xlsm"
wsName = "sheet1"
cellRef = "E2"

Ret = "'" & wbPath & "[" & wbName & "]" & _
      wsName & "'!" & Range(cellRef).Address(True, True, -4150)

ActiveWorkbook.Worksheets("sheet1").Range("A14").Value = ExecuteExcel4Macro(Ret)

Function getValue(wbPath As String, wbName As String, wsName As String, cellRef As String)
    Dim Ret As String
    Ret = "'" & wbPath & "[" & wbName & "]" & _
            wsName & "'!" & Range(cellRef).Address(True, True, -4150)
    getValue = ExecuteExcel4Macro(Ret)
End Function

Sub test()
    Dim i As Integer, wbs

    wbs = Array("overview 2014.xlsm", "workbook2.xlsm", _
                "workbook3.xlsm", "workbook4.xlsm", _
                "workbook5.xlsm", "workbook6.xlsm", _
                "workbook7.xlsm", "workbook8.xlsm", _
                "workbook9.xlsm", "workbook10.xlsm")
    ' LBound(wbs) = 0
    For i = LBound(wbs) To UBound(wbs)
        ActiveWorkbook.Worksheets("sheet1").Range("A4").Offset(i).Value = _
            getValue("http://*****/2014/", CStr(wbs(i)), "sheet1", "E2")
    Next i
End Sub