Sub TEST1()
'VLookup関数で、別ブックを参照
ThisWorkbook.Worksheets("Sheet1").Range("A2") = WorksheetFunction.VLookup("D", Workbooks("TEST.xlsx").Worksheets("Sheet1").Range("A2:B10"), 2, False)
End Sub
ちょっと長いので「With」でくくっておきます。
「With」でくくる
Sub TEST2()
'「With」でくくる
With Workbooks("TEST.xlsx").Worksheets("Sheet1")
ThisWorkbook.Worksheets("Sheet1").Range("A2") = WorksheetFunction.VLookup("D", .Range("A2:B10"), 2, False)
End With
End Sub
という感じになります。
ポイントは、「別ブックを開いておく」ということです。
別ブックを開いておく
では、VBAコードを実行してみます。
VLookup関数で「別ブック」を参照
VLookup関数で「別ブック」を参照できました。
別ブックを開いておく必要がある
「WorksheetFunction」を使う場合は、別ブックを開いておく必要があります。
別ブックを閉じた状態で実行するとエラーとなってしまいます。
ちょっとやってみます。
Sub TEST3()
'別ブックを開いていない場合は、エラーとなる
With Workbooks("TEST.xlsx").Worksheets("Sheet1")
ThisWorkbook.Worksheets("Sheet1").Range("A2") = WorksheetFunction.VLookup("D", .Range("A2:B10"), 2, False)
End With
End Sub
Sub TEST4()
'別ブックを開く
Workbooks.Open "C:\TEST\TEST.xlsx"
'別ブックを、VLookup関数で参照
With Workbooks("TEST.xlsx").Worksheets("Sheet1")
ThisWorkbook.Worksheets("Sheet1").Range("A2") = WorksheetFunction.VLookup("D", .Range("A2:B10"), 2, False)
End With
'別ブックを閉じる
Workbooks("TEST.xlsx").Close
End Sub
別ブックは閉じた状態にしておきます。
別ブックは閉じた状態
では、VBAコードを実行してみます。
別ブックが開かれます。
別ブックが開かれる
別ブックを参照して、閉じられます。
別ブックを参照して閉じる
別ブックを参照して閉じました。
こんな感じで、「別ブックを開いて閉じる」VBAコードを追加すると便利です。
埋め込み数式を使ってVLookup関数で別ブックを参照
次は、「埋め込み数式」を使ってVLookup関数で「別ブック」を参照してみます。
「数式埋め込み」は別ブックを「閉じた状態」でも参照できるので便利です。
「ブック名」を入力して別ブックを参照
「埋め込み数式」を使う場合は「ブック名」までを入力して別ブックを参照します。
「[TEST.xlsx]Sheet1!$A$2:$B$10」という感じです。
Sub TEST5()
'数式埋め込みでVLookup関数を使って、別ブックを参照
With ThisWorkbook.Worksheets("Sheet1")
.Range("A2") = "=VLOOKUP(""D"",[TEST.xlsx]Sheet1!$A$2:$B$10,2,FALSE)"
.Range("A2").Value = .Range("A2").Value '値に変換
End With
End Sub
Sub TEST6()
'数式埋め込みで、別ブックを「フルパス」で参照
With ThisWorkbook.Worksheets("Sheet1")
.Range("A2") = "=VLOOKUP(""D"",'C:\TEST\[TEST.xlsx]Sheet1'!$A$2:$B$10,2,FALSE)"
.Range("A2").Value = .Range("A2").Value '値に変換
End With
End Sub
では、別ブックを閉じた状態で実行してみます。
別ブックを閉じたまま参照できる
別ブックを閉じたまま参照できました。
「フルパスの数式」を作成する手順を簡単に解説します。
フルパスの数式を作成する手順
別ブックは、同じフォルダに保存しています。
別ブックを開いた状態でセルに数式を入力します。
問題なくVLookup関数で参照できたら、別ブックを閉じます。
別ブックを閉じると自動的に、セル範囲が「フルパス」になります。
フルパスになった数式をコピーして、VBAコードにコピーです。
これで、別ブックを閉じたままで参照できます。
こんな感じで、別ブックを閉じたままで参照できます。
別ブックを閉じた状態で参照すると高速
別ブックを閉じた状態で参照したいのは、「高速」だからです。
ちょっとためしてみます。
別ブックを開いて参照
別ブックを開いて参照するのを単純に「10回」繰り返してみます。
Sub TEST7()
t = Timer
'10回ループする
For i = 1 To 10
'別ブックを開く
Workbooks.Open "C:\TEST\TEST.xlsx"
'別ブックを、VLookup関数で参照
With Workbooks("TEST.xlsx").Worksheets("Sheet1")
ThisWorkbook.Worksheets("Sheet1").Range("A2") = WorksheetFunction.VLookup("D", .Range("A2:B10"), 2, False)
End With
'別ブックを閉じる
Workbooks("TEST.xlsx").Close
Next
Debug.Print Timer - t & " 秒"
End Sub
VBAコードを実行してみます。
10回だけ別ブックを開いて参照しました。
かかった時間は、「8.98 秒」です。
ちょっと遅いかなという感じです。
別ブックを閉じたまま参照
次は、別ブックを「閉じたまま」参照するのを「10回」繰り返してみます。
Sub TEST8()
t = Timer
'10回ループする
For i = 1 To 10
'数式埋め込みで、別ブックを「フルパス」で参照
With ThisWorkbook.Worksheets("Sheet1")
.Range("A2") = "=VLOOKUP(""D"",'C:\TEST\[TEST.xlsx]Sheet1'!$A$2:$B$10,2,FALSE)"
.Range("A2").Value = .Range("A2").Value '値に変換
End With
Next
Debug.Print Timer - t & " 秒"
End Sub
