|
ベタ書きしますので、エラーがあったら報告して下さい。
リンクを張って値を持ってくるコードです。
Sub Test_Link()
Dim i As Integer, j As Integer
Dim LkS1 As String, LkS2 As String
Dim Flg As Boolean
j = 5
For i = 111 To 555 Step 111
With ThisWorkbook
LkS1 = "='" & .Path & "\[A" & i & ".xls]日報A'!"
LkS2 = "='" & .Path & "\[A" & i & ".xls]日報B'!A1"
With .Worksheets(1)
If Flg = False Then
.Cells(j, 27).Formula = LkS1 & "A1"
.Cells(j, 28).Formula = LkS1 & "B1"
.Cells(j, 2).Value = _
.Cells(j, 27).Value & .Cells(j, 28).Value
Flg = True
Else
.Cells(j, 2).Formula = LkS1 & "A1"
.Cells(j, 4).Formula = LkS2
End If
End With
End With
j = j + 1
Next i
With ThisWorkbook.Worksheets(1)
.Cells.Copy
.Range("A1").PasteSpecial xlPasteValues
.Tange("AA:AB").ClearContents
End With
Application.CutCopyMode = False
End Sub
|
|