Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement