|
Sub MyData_Link()
Dim i As Integer
Dim Sh As Worksheet
Dim MyF As String, LkSt As String
Const SFol As String = _
"C:\Documents and Settings\User\My Documents\Exl_Files"
Set Sh = Worksheets("一覧")
For i = 1 To 90
MyF = Dir(SFol & "\" & Format(i, "00") & "*.xls")
With Sh.Range(Sh.Cells(i + 7, 2), Sh.Cells(i + 7, 12))
If MyF = "" Then
.Cells(1).Value = "File_NotFound"
Else
LkSt = "='" & SFol & "\[" & MyF & "]Sheet1'!"
.Cells(1).Formula = LkSt & "$A$1"
.Cells(3).Formula = LkSt & "$A$2"
.Cells(5).Formula = LkSt & "$B$1"
.Cells(6).Formula = LkSt & "$B$2"
.Cells(8).Formula = LkSt & "$C$1"
.Cells(11).Formula = LkSt & "$C$2"
.Value = .Value
End If
End With
Next i
Set Sh = Nothing
MsgBox "全てのデータを転記しました", 64
End Sub
で、どうでしょーか ?
|
|