|
値を引っ張るだけなら、ブックを開かなくてもリンクを使って出来ます。
概ね以下のようなコードになります。
Sub Data_Link()
Dim WB As Workbook
Dim Flg As Boolean
Dim MyF As String, LkSt1 As String, LkSt2 As String
Const Fol As String = _
"C:\Documents and Settings\AYUMI\デスクトップ"
ChDir Fol
MyF = Application _
.GetOpenFilename("Microsoft Excelブック(*.xls),*.xls")
If MyF = "False" Then Exit Sub
For Each WB In Workbooks
If WB.Name = "書き込みデータ.xls" Then
Flg = True: Exit For
End If
Next
If Flg = False Then
Workbooks.Open ThisWorkbook.Path & "\書き込みデータ.xls"
End If
LkSt1 = "='" & Fol & "\[" & Dir(MyF) & "]Sheet1'B2"
LkSt2 = "='" & Fol & "\[" & Dir(MyF) & "]Sheet1'B9"
With Workbooks("書き込みデータ.xls").Worksheets(1)
With .Range("B3:F7")
.Formula = LkSt1
.Value = .Value
End With
With .Range("B9:F13")
.Formula = LkSt2
.Value = .Value
End With
End With
Workbooks("書き込みデータ.xls").Save
End Sub
|
|