－ちょっと使えるネタを紹介－
Excel VBAで、別ブックを参照する方法について、ご紹介します。簡単な方法として、「別ブックを開いて参照する方法」があります。高速に別ブックを参照したい場合は、「数式を使って参照する方法」が使えます。具体的なVBAコードを使って、解説してきます。
この記事では、別ブックを参照する方法について、ご紹介します。
簡単に別ブックを参照したい場合は、「別ブックを開いて参照する方法」があります。
少し見づらくてもいいので、高速に別ブックを参照したい場合は、「数式を使って別ブックを参照する方法」が使えます。
では、別ブックを参照する方法について、解説していきます。
この記事で紹介すること
目次から見たい項目へ移動すると便利ですよ。
目次
VBAで、別ブックを参照する場合は、「Workbooks」を使います。
別ブックを参照する手順としては、
という流れです。
VBAコードだと、次のようになります。
Sub TEST1()
'別ブックを開く
Workbooks.Open Filename:=ThisWorkbook.Path & "\TEST.xlsx"
Set Wb1 = ThisWorkbook 'このブック
Set Wb2 = Workbooks("TEST.xlsx") '別ブック
'別ブックの値を取得
Wb2.Worksheets("Sheet1").Range("A1:C1").Copy Wb1.Worksheets("Sheet1").Cells(1, 1)
Wb2.Close False '別ブックを閉じる
End Sub
同じフォルダに、「TEST.xlsx」というブックを用意しました。
「TEST.xlsx」には、「Sheet1」に次にように値を入力しておきました。
では、VBAコードを実行して、別ブックを参照してみます。
別ブックの値を、参照することができました。
この方法がVBAとしては、シンプルな方法になります。
ただ、ブックを開いて参照するので、少し時間がかかります。
次で、時間を計測してみます。
別ブックを開いて参照する方法で、100個のブックを参照するのにかかる時間を、計測してみます。
VBAコードは、こんな感じになります。
Sub TEST2()
t = Timer
For i = 1 To 100
'別ブックを開く
Workbooks.Open Filename:=ThisWorkbook.Path & "\TEST" & Format(i, "000") & ".xlsx"
Set Wb1 = ThisWorkbook 'このブック
Set Wb2 = Workbooks("TEST" & Format(i, "000") & ".xlsx") '別ブック
'別ブックの値を取得
Wb2.Worksheets("Sheet1").Range("A1:C1").Copy Wb1.Worksheets("Sheet1").Cells(i, 1)
Wb2.Close False 'ブックを閉じる
Next
Debug.Print Timer - t & " 秒"
End Sub
100個のブックを用意しました。
それぞれファイルには、「Sheet1」にデータを入力しています。
では、VBAコードを実行して、100個の別ブックを開いて、参照してみます。
100個の別ブックを参照できました。
問題のかかった時間です。
「87.70 秒」となりました。
結構、時間かかりますね。
大量の別ブックを参照したい場合は、次に紹介する数式を使って参照する方法を使うと、高速化できます。
別ブックを参照する方法として、数式を使って参照する方法があります。
セルで、「＝数式」と入力して参照するやつです。
これが、結構、高速です。数式を使って別ブックを参照してみます。
数式を使って、別ブックを参照する方法です。
まず、元のブックと、参照先のブックを開いて、参照したいセルを「＝」で持ってきます。
すると、参照先の別ブックの値を取得することができます。
参照先の別ブックを閉じると、セルに入力した数式が、フルパス表示になります。
フルパス表示になりました。
='C:\Users\User\Desktop\TESTT\[TEST.xlsx]Sheet1'!$A$1
このフルパスの数式を使えば、別ブックを参照することができます。
ちなみに、ネットワークドライブの別ブックも、参照することができます。
別ブックが閉じていても、別ブックを参照できる点が、ポイントです。
では、VBAで数式を使って、別ブックを参照してみます。
先ほどの数式で入力した値を使って、別ブックのセル「A1」を参照するVBAコードです。
Sub TEST3()
With ActiveSheet
'数式を使って、別ブックを参照
.Cells(1, 1) = "='C:\Users\User\Desktop\TEST\[TEST.xlsx]Sheet1'!$A$1"
'数式を値に変換
.Cells(1, 1).Value = .Cells(1, 1).Value
End With
End Sub
最後に、数式を値に変換しています。
フォルダ構成は、次のようにしています。
元ブックが「00_別ブックを参照.xlsm」で、参照先のブックが「TEST.xlsx」となります。
参照先のブック「TEST.xlsx」には、次のように、値が入力されています。
では、VBAコードを実行してみます。
別ブックのセルA1を参照できました。
ただし、このままの数式「='C:\Users\User\Desktop\TESTT\[TEST.xlsx]Sheet1'!$A$1」では、応用ができませんので、フォルダパスやブック名、シート名、セル範囲を変数にしてみます。
Sub TEST4()
Dim A, B, C, D
With ActiveSheet
A = ThisWorkbook.Path 'フォルダパス
B = "TEST.xlsx" '別ブック名
C = "Sheet1" 'シート名
D = .Cells(1, 1).Address 'セル範囲
'数式を使って、別ブックを参照
.Cells(1, 1) = "='" & A & "\[" & B & "]" & C & " '!" & D
'数式を値に変換
.Cells(1, 1).Value = .Cells(1, 1).Value
End With
End Sub
こんな感じで、ブック名やシート名、セル範囲を変数にすると応用がききます。
では、実行してみます。
同じように別ブックのセルA1を参照することができました。
あとは、別ブックのセル「A1～C1」の範囲のデータを参照したいので、Forでループして、データを参照してみます。
Sub TEST5()
Dim A, B, C, D
With ActiveSheet
A = ThisWorkbook.Path 'フォルダパス
B = "TEST.xlsx" '別ブック名
C = "Sheet1" 'シート名
For i = 1 To 3
'セル範囲
D = .Cells(1, i).Address
'数式を使って、別ブックを参照
.Cells(1, i) = "='" & A & "\[" & B & "]" & C & " '!" & D
'数式を値に変換
.Cells(1, i).Value = .Cells(1, i).Value
Next
End With
End Sub
では、別ブックのセル「A1～C1」を参照してみます。
別ブックのセル「A1～C1」を参照することができました。
VBAコードが少し見づらいではありますけども、かなり高速です。
次で、検証してみます。
100個のブックを、数式を使って参照するという方法で、時間を計測してみます。
VBAコードは、次のようになります。
Sub TEST6()
t = Timer
Dim A, B, C, D
For i = 1 To 100
For j = 1 To 3
With ActiveSheet
A = ThisWorkbook.Path 'ファルダパス
B = "TEST" & Format(i, "000") & ".xlsx" '別ブック名
C = "Sheet1" 'シート名
D = Cells(1, j).Address 'セル範囲
'数式を使って、別ブックを参照
.Cells(i, j) = "='" & A & "\[" & B & "]" & C & " '!" & D
'数式を値に変換
.Cells(i, j).Value = .Cells(i, j).Value
End With
Next
Next
Debug.Print Timer - t & " 秒"
End Sub
別ブックを100個、用意しました。
それぞれのブックには、セル「A1～C1」の範囲にデータが入力されています。
では、100個の別ブックを、数式を使って参照してみます。
100個の別ブックを、数式を使って参照できました。
問題の時間です。
「1.41 秒」となりました。
かなり速いですよね。別ブックを毎回開いて参照した場合では、「87.70 秒」でした。
ブックを閉じたまま参照できるので、速いです。
ちなみに、配列を使うともうちょっと速くなります。
次で検証してみます。
数式を使って別ブックを参照する方法で、さらに配列を使って、高速化してみます。
VBAコードは、こんな感じになります。
Sub TEST7()
t = Timer
'配列を作成
Dim Arr
ReDim Arr(1 To 100, 1 To 3)
Dim A, B, C, D
For i = 1 To 100
For j = 1 To 3
With ActiveSheet
A = ThisWorkbook.Path 'フォルダパス
B = "TEST" & Format(i, "000") & ".xlsx" '別ブック名
C = "Sheet1" 'シート名
D = Cells(1, j).Address 'セル範囲
'数式を配列に格納
Arr(i, j) = "='" & A & "\[" & B & "]" & C & " '!" & D
End With
Next
Next
With ActiveSheet
'配列をセルに入力
.Range("A1").Resize(UBound(Arr, 1), UBound(Arr, 2)) = Arr
'数式を値に変換
.Range("A1").CurrentRegion.Value = .Range("A1").CurrentRegion.Value
End With
Debug.Print Timer - t & " 秒"
End Sub
先ほどと同じ100個の別ブックを使います。
それぞれのブックには、セル「A1～C1」の範囲にデータが入力されています。
では、実行します。
まずは、同じように100個の別ブックを参照できました。
では、問題の時間です。
「0.37 秒」となりました。
配列を使わないで、セルに入力した場合は、「1.41 秒」でしたので、少しだけ速くなっています。
配列を使って、一括でセルに入力したので、その分速くなったという感じです。
別ブックを、高速に参照したい場合は、数式を使って参照する方法を、検討してみる価値ありです。
この記事では、別ブックを参照する方法について、ご紹介しました。
簡単に別ブックを参照したい場合は、別ブックを開いて参照する方法があります。
少し見づらくてもいいので、高速に別ブックを参照したい場合は、数式を使って別ブックを参照する方法が使えます。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す
カテゴリから見たい項目を探すと便利ですよ。
↓キーワードを入力する